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
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.