BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
So why when you create this simple ranking measure:
Measure giving the rank of each product by sales value.
using this formula:
=RANKX(Product,'Transaction'[Sum of SalesValue])
does it give these results?
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.
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?
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:
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!
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!