none
DAX measure for calculating Actual vs Target Score

    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 (defined in Targets table for each Product Type) 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

    Friday, February 15, 2013 10:50 PM