none
Rank function

    Question

  • 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.

    Thx

    Matt
    Friday, November 27, 2009 11:18 AM

Answers

All replies

  • Hi, Matt,

    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
    CustomerSales=sum('FactAllSales'[SalesAmount])

    3. create a calculated measure for ranking the customer with the following formula
    CustomerRank
    = COUNTROWS(
      FILTER (
         ALL(DimCustomer[CustomerName]),
        DimCustomer[CustomerSales](Values(DimCustomer[CustomerName])) <  DimCustomer[CustomerSales]
       && DimCustomer[CustomerName] <>  VAlues( DimCustomer[CustomerName])
     )
    )+1

    Please let us know if this is what you are looking for.

    Thanks!

    Lisa
    • Proposed as answer by Rob CollieMVP Monday, December 28, 2009 6:56 AM
    Monday, November 30, 2009 5:22 PM
  • Thanks v. much, Lisa, I'll give it a go.
    Tuesday, December 01, 2009 1:41 PM
  • Works like a charm! Thanks.
    Tuesday, December 01, 2009 2:36 PM