PowerPivot Denali new DAX function to rank (RANKX)

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 :).