In Denali we introduced a new function to RANK a column based on a measure. In this blog post I will show you how to use the RANKX function to rank the values of a measure.
Lets look at this scenarion:
I have salesamounts per regions sliced by year:
Now I want to get the rank of the regions by salesamount. I use the RANKX function to get this ranking:
=RANKX(ALL(Regions[Region]) ,Sales[Sum of SalesAmount])
So what does this mean? This function will rank the [Sum of SalesAmount] for the current Row against the [Sum of SalesAmount] for all the other rows in the Regions column. The ALL(Regions[Region]) will give the rank function the values to compare the current row against. One thing to notice here is that I use a already existing measure Sales[Sum of SalesAmount] instead of SUM(Sales[SalesAmount)). The reason for that is that SUM(Sales[SalesAmount)) is not able to be calculated against each and every row for ALL(Regions[Region]) but will be evaluated against the total result. If you would wrap a CALCULATE around the measure it would be able to evaluate the value for each and every row. A measure is automatically wrapped around CALCULATE() by the engine.
Using the above RANK measure gives the following ranking:
In the case of “Europe – France” it will Rank the [Sum of SalesAmount] for “Europe – France” against the values of [Sum of SalesAmount] for all other rows in the Regions column (yellow), resulting in Rank 7.
One interesting thing we see here is that the results now have empty values as well. That is because of the ALL(Regions[Region]) parameter in the RANKX function will make sure it will compare the current value against any other value in this column, even when it is unique. So we want to remove those rows, lets wrap a check araound the rank to exclude those rows that don’t have a value for the salesamount:
=if(not(ISBLANK([Sum of SalesAmount])), RANKX(ALL(Regions[Region]) ,Sales[Sum of SalesAmount]) , blank())
This gives this end result:
Of course this is just a simple example but much more is possible with the Rank function :).
2 Replies to “PowerPivot Denali new DAX function to rank (RANKX)”
Hi! Can you use RANKX to display a very similar table, but that has the “CalendarYear” across the top, and Regions down the side as rows.
The regions would be then ranked by a *specific year* (say 2003), but all years would be displayed in a table.
Row Labels 2001 2002 2003 2004
I’m having a problem when i have NEGATIVE net sales.
When i rank by sales person within the region, and the sales person has had negative net sales (more returns than sales), the ranking that it gives me is that of the total population, not within its own regions.
Region 1 has 30 sales people, but the whole organization has 90 sales people. Two of Region 1’s salesmen have negative sales. When i use the following formula, it will return 89 and 90 for these two individuals, instead of 29, 30. (even though i am pivoting by selling region).
=RANKX(ALL(SalesAnalysis[sa_rep_name]), [sa_Net Sales], , 0, skip)
Any help is appreciated!