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
    Moderator
  • 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
  • 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 Greg2178 Monday, October 27, 2014 4:00 PM
    Saturday, October 25, 2014 2:06 PM