dax logic...

Question

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

Answer 1

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
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

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

Answer 3

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

Answer 4

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.

---

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

Answer 5

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

Answer 6

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
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 7

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