WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
Using the RANKX function in PowerPivot DAX measures
The RANKX function works out where a measure lies in order for any dimension, but it has a couple of gotchas to watch out for, as this blog explains.

Posted by Andy Brown on 04 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating measures using RANKX in PowerPivot - part 2

I have learnt many software products in my time, but it's PowerPivot which stretches my brain cells most!  The software concept is deceptively simple, but interpreting DAX functions can fry the brain.  The RANKX function is a good example.

I've previously blogged about the syntax of this function when creating calculated columns in a PowerPivot table - this blog considers what happens when you use the RANKX function in a measure.

A basic RANKX function, showing what can go wrong

The syntax of the RANKX function is:

=RANKX( Table ranking over, Expression being ranked )

So why when you create this simple ranking measure:

Rank by sales value

Measure giving the rank of each product by sales value.


using this formula:

=RANKX(Product,'Transaction'[Sum of SalesValue])

does it give these results?

Ranking - all showing 1

Everything appears as being of the highest rank!


The solution - use ALL

What PowerPivot is doing above is to rank each sales value against the set of products for the current filter context

Crocky sales value

It's not surprising that total sales for Crocky is the highest total sales value for Crocky!


So for the Crocky product above, for example, it's inevitable that the sales value of 116094.915 is the highest-ranking amount for the cell shown, since Crocky is the only product which contributes to this filter context. 

The answer is to supply the dimension against which we are ranking:


So now we're calculating the total sales value of transactions for the current filter context, and ranking this relative to the sum for all of the products.  Or are we?

Ranking still not working

The change in formula doesn't seem to have made much difference ...

The final tweak we need to make is to use a measure already created, rather than creating one on the fly:

'Transaction'[Total sales])

Why should it make any difference whether you use a pre-calculated total, or calculate it within the measure?  Here's an answer.

I've read the webpage linked to here several times and still don't understand it, but I wanted to make sure that this blog was up to date!

Oh, and just to give a sense of achievement, here's what our pivot table will now look like! 

The final pivot table, with ranking

Sales for each product are ranked against total sales for the product.


This  may be a complicated approach, but it's still simpler than  using the alternative EARLIER function!


This blog has 0 threads Add post