dax logic...

# dax logic...

• Friday, February 15, 2013 10:38 PM

Hello, I need help, I feel as with your help I'll have the DAX "Aha!" moment... I have Sales Fact Table with CustomerID, Purchase date, ProductID, SalesAmount. Also there's Customers table with an hierarchy: Department, Account manager, customer name, customerid Products table with hierarchy: Product Type, Product Group, Productid Targets Table specifies goals for each Department per Product Type I've built relationships as Brent suggested in this post: http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/7d06ff1b-04fd-496e-9286-4babd0c9beb9/ The challenge is to build the SCORE measure. the Score is calculated as follows: if a customer has bought 3 or more Product types above the Goal amount (in Targets table) then the Score is 1. Otherwise, it's 0. I'm struggling to build this measure because my Fact table is at transaction level, so I don't want the calculation to sum up all rows. It is important that I'll be able to show sum of the Score at. Account manager and Department levels in my reports. That's it. I have a feeling DAX is made for these kind of issues and there should be a very elegant solution Please help Thank you Michael

Michael

### All Replies

• Saturday, February 16, 2013 4:28 AM

Michael -

Here's one way to accomplish this.  I actually posted a different approach to your original question (link you reference above).  One that allows the Target table to just be a disconnected table at the rolled up grain, and use the LOOKUPVALUE function to get the target for each productCategory / CustomerSegment.

For your Score measure above, here's what I came up with.  Several separate measures that build upon one another (easier to troubleshoot this way).

1. Lookup the Target by grouping the Sales data to the same grain as the Target.

```=SUMX(SUMMARIZE(Sales
,Sales[ProductCategory]
,Sales[CustomerSegment]
,"TargetAmt"                ,LOOKUPVALUE(Target[Target]                             ,Target[CustomerSegment],Sales[CustomerSegment]                             ,Target[ProductCategory],Sales[ProductCategory]                            )
)
,[TargetAmt]
)```

2. Calculate the number of Target hits at the customer level

```=SUMX(SUMMARIZE(Sales
,Sales[Customer]
,Sales[ProductCategory]
,"Hit"
,IF([SumSales] >= [SumxTarget],1,0)
)
,[Hit]
)```

3. Then compare each customer's number of hits to the other target (3), and only count the ones that met or exceeded the 3.

```=SUMX(VALUES(Sales[Customer])
,IF([TargetHits] >= 3          ,1          ,BLANK()         )
)```

With that, you can roll up the customer count to the Account Manager and Department level as needed.  The prototype I built to test this produces this pivot with the rollups you're looking for.

Let me know if that's what you were going for.  Here's a link to the linked-table prototype (2013) on my SkyDrive for reference.  Hope that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Edited by Saturday, February 16, 2013 4:30 AM
• Marked As Answer by Saturday, February 16, 2013 2:52 PM
•
• Saturday, February 16, 2013 9:01 AM

Brent, thank you very much for your fast and detailed response. I will try it first thing tomorrow morning and let you know how it worked out Michael

Michael

• Saturday, February 16, 2013 1:31 PM

WOW!!!

Brent, I couldn't wait till the workweek to try your solution :)

Seems to be working !!!!!!

I am still playing with the data in excel and I'll have to understand each formula at intuitive level (to get to the "Aha" moment...)

But I think your solution fits perfectly in my model!

Thanks a lot!

Michael

Michael

• Saturday, February 16, 2013 1:57 PM

Glad to hear it Michael.  And appreciate the feedback.  DAX is powerful stuff, huh.

Let me know if you hit any other snags.  And please remember to mark helpful posts and correct answers.

Cheers.

• Saturday, February 16, 2013 4:04 PM

Hi Brent,

After playing with Pivot, I found a place where I need to adjust the solution.

It happens that we also need to measure the Sales People by the same Score as Account Managers, and:

Each Customer can have different Product Categories sold by DIFFERENT Salesperson. The Salesperson's name is in the FACT table and so is their hierarchy (Department, Team)

So, for example, Customer ABC Inc. has 1 as a Score (since he has 4 Target Hits).

If I look at a Salesperson hierarchy, I see that "Catherine's part" doesn't get green. I understand why - she has sold only one ProductCategory out of 4, but it is important for me that she also gets 1 (since she has sold to a Customer with OVERALL 4 TargetHits).

I feel that your solution can also fit in this case, but I am struggling with the adjustment. It's important that it also could be rolled up to the hierarchy as sum

Thank you very much in advance

Michael

Michael

• Monday, February 18, 2013 4:35 PM

Hi Michael -

Think I got it.  I changed the evaluation context for the Score measure's IF that returns 1 or 0.  Wrapped it in a CALCULATE and released the filter on the SalesPerson table.  This way, when slicing by the SalesPerson, you should get a hit for any customer that hit the target like you want.

```=SUMX(VALUES(Sales[Customer])
,CALCULATE(IF([TargetHits] >= 1, 1, BLANK())
,ALL(SalesPerson)
)
)```

One note: With this approach, you get the scores you want at the SalesPerson level, but you don't get double-counting of customers at levels above SalesPerson.  Hopefully that's what you're going for.

Let me know if that helps.  And I updated the file on my SkyDrive with this change if you want to grab a new copy and check it out.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Saturday, March 02, 2013 7:55 PM

Hi Brent,

Thank you again for the reply but I think it is not working...

I've downloaded you file, changed some numbers and this what it shows for Customer 1

Without Salespeople on rows, Customer1 had 2 hits

so I need it to show 2 for salesperson 2 also

Somehow "ALL(SalesPerson)" doesn't work...

Thanks

 Row Labels SumxTarget SumSales TargetHits Score SalesPerson1 290 975 2 1 Customer1 290 800 2 1 ProductCat1 155 500 1 1 ProductCat2 135 300 1 1 Customer3 290 175 0 ProductCat1 155 150 0 ProductCat2 135 25 0 SalesPerson2 405 290 1 1 Customer1 135 50 0 ProductCat2 135 50 0 Customer2 270 35 0 ProductCat1 145 15 0 ProductCat2 125 20 0 Customer4 270 205 1 1 ProductCat1 145 150 1 1 ProductCat2 125 55 0 Grand Total 560 1,265 3 2

Michael