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.

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:

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

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,

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.

Refreshing the pivot table,

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.

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

Thank’s, It helps me very much.

Great!

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

Sure, I’ll email you my email address.

Thanks,

Ayad

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

I am also have the same issues with the RankX producing all 1’s. Was there a resolution?

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/

Thank you, Thank you Mr. Ayad. I was missing the FILTER function.

Heya i amm for the first time here. I found this board and I

find It really useful & it helped me out a lot. I hope too give something back

and aid others like you aided me.