# 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

• Works like a charm! Thanks.
• Marked as answer by Friday, March 5, 2010 8:13 PM
Tuesday, December 1, 2009 2:36 PM

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