DAX RANKX function scenarios

RANKX is a DAX function introduced in SQL Server PowerPivot 2012. It returns the ranking of a number in a list of numbers for each row in the table argument. For more information about RANKX, please click here .

Let’s see it in action, here is my scenario

I have a list of hospital clinical departments (to simplify the example, I’m using 2 departments) and would like to rank the providers in each department by revenue. I started with creating a table in Excel “Table1” and add it to PowerPivot model.

1-Rankx-Table1

I used the following measures to create a simple Rank function:

TotalRevenue:=SUM([Revenue])

RankDesc:=RANKX(All(Table1[Provider]),[TotalRevenue],,,Dense)

RankAsc:=RANKX(All(Table1[Provider]),[TotalRevenue],,1,Dense)

The RankAsc and RankDesc measures will rank the [TotalRevenue] for the current Row against the [TotalRevenue] for all the other rows in the Revenue column. The ALL(Table1[Provider]) is the list of values that the rank function use to compare the current row against. The difference between the two rank measures is the Order of rank value (low to high or high to low). One thing to notice here is that I use an existing measure TotalRevenue.

Using the above RANK measures get the following ranking:

2-Rankx-Result1

Now, when I filter on Medicine department, I get the following

3-Rankx-Result3

The descending rank works ok but the ascending rank starts with 2, why is that?

Let me explain, I was ranking the set of providers in ALL(Table1[Provider]) which includes everyone, not just the folks in the one department.

When I filter on Medicine, the folks who are not in the Medicine department are still in the set that is being ranked. The use of ALL() means that I am ranking against an unfiltered set of all the providers, not just the providers in the selected department.

To fix this issue, I’ve modified the measure for RankAsc, here is the formula instead:

RankAsc=RANKX(FILTER(All(Table1[Provider]),[TotalRevenue]),[TotalRevenue],,1,Dense)

The part I added is highlighted in red, and it says to only consider the providers who have non-zero revenue.

Now, when I view the pivot table and filter on Medicine, as you can see it is ranking correctly,

4-Rankx-Result4

So far so good, but what if I have a provider with no revenue (NULL value) in the Medicine department. I replaced the provider “Gail” revenue with NULL value.

5-Rankx-Result5

Refreshing the pivot table,

6-Rankx-Result6_1

As you can see above, that Gail is Rank = 1 in ascending order, that is because the RANK =1 position is going to the items that are tied with no data.  In other words, if there are any items in the column for whom the measure is BLANK(), they would all be tied for position =1 when you rank in ascending order.

So to fix this issue, I modified the DAX formula to:

RankAsc=IF(NOT(ISBLANK([TotalRevenue])),RANKX(FILTER(All(Table1[Provider]),[TotalRevenue]),[TotalRevenue],,1,Dense), BLANK())

RankDesc=IF(NOT(ISBLANK([TotalRevenue])),RANKX(FILTER(All(Table1[Provider]),[TotalRevenue]),[TotalRevenue],,,Dense), BLANK())

The above formula will check the TotalRevenue measure for a provider, if it is not blank then rank, otherwise ignore it.

I got the required result which doesn’t show a rank for a NULL value.

7-Rankx-Result7

This just a simple example but you can do much more with the Rankx function.

9 thoughts on “DAX RANKX function scenarios

  1. Hello Ayad

    Thanks for the solution; however, i am getting all 1’s for both Asc/Desc ranking. Could i email you a screenshot of what am getting?

    Thanks

  2. Pingback: DAX RANKX function – Part II | Ayad Shammout's SQL & BI Blog

    • Hi Alison,

      RANKX function is not dynamic and you need to specify the rank by column by using ALL function to ignore the filter context on the column you are ranking by. If you add additional fields to PivotTable, you need to add those fields to the ALL function to ignore the filter context in order to rank by multiple columns. In my example, I’m ranking by provider so the DAX function is using ALL function to ignore the filter context on Provider field.
      Rank:=RANKX(All(Table1[Provider]),[TotalRevenue],,,Dense)

      You can read my new post RANKX Part II with an example on how to use RANKX function and calculate at different levels. https://ayadshammout.com/2014/02/23/dax-rankx-function-part-ii/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s