Rank function RRS feed

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


    Friday, November 27, 2009 11:18 AM


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

    3. create a calculated measure for ranking the customer with the following formula
      FILTER (
        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 Collie 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 1, 2009 1:41 PM
  • Works like a charm! Thanks.
    • Marked as answer by Mardin Pena Friday, March 5, 2010 8:13 PM
    Tuesday, December 1, 2009 2:36 PM
  • FYI since Lisa's original good answer, the RANK.EQ and RANKX functions were released, which do perform ranking in PowerPivot now!  :)

    RANK.EQ:  http://msdn.microsoft.com/en-us/library/gg492185.aspx

    RANKX:  http://msdn.microsoft.com/en-us/library/gg492169.aspx

    • Proposed as answer by greggyb Monday, October 27, 2014 4:00 PM
    Saturday, October 25, 2014 2:06 PM