Hi, does anyone know how to create a calculated measure that ranks another measure ([sales volume] in this case) within a pivottable? This can be done easily enough outside the table, but I was wondering why there is no DAX Rank() function in the list of available functions? Ditto percent.
Here is one example to get rank on customers based on the sales amount measure using the sample AdventureWorks database:
1. Create a calculated column for Customer full name by concatenating the first and the last name
='DimCustomer'[FirstName] & ", " & 'DimCustomer'[LastName]
2. Create a calculated measure for customer sales amount in pivot table
3. create a calculated measure for ranking the customer with the following formula
DimCustomer[CustomerSales](Values(DimCustomer[CustomerName])) < DimCustomer[CustomerSales]
&& DimCustomer[CustomerName] <> VAlues( DimCustomer[CustomerName])
Please let us know if this is what you are looking for.
- Proposed as answer by Rob CollieMVP Monday, December 28, 2009 6:56 AM
FYI since Lisa's original good answer, the RANK.EQ and RANKX functions were released, which do perform ranking in PowerPivot now! :)
- Proposed as answer by Greg2178 Monday, October 27, 2014 4:00 PM