Rankx with averages

DAX DAX Calculations
1

I have a ranking formula which works on individual rows of data. However when the rows are aggregated it returns ranking numbers that are too high 6,20,28 instead of 1,2,3. The names are actually ranked correctly, but I am not sure why they are so high.

The formula I am using in both cases is :

Rank 2019 = CALCULATE(
RANKX (ALLSELECTED(NSS), CALCULATE(AVERAGE(‘NSS’[Actual value]), ‘NSS’[Year] IN { 2019 }) ) )

I have attached two screenshots - one where the ranking is working on data at the lowest level 1,2,3, the other on aggregated data 6,20,28

Presentation2
Presentation2.jpg1280×720 180 KB

Presentation3
Presentation3.jpg1280×720 156 KB

2

This will all be because of the virtual table within the RANKX function and how it is reacting to the new context you have placed in the table.

It’s very difficult to know the exact answer without see and testing out the scenario in a demo file.

I covered RANKX in detail in the below workshop. It can be a really tricky formula to work with when you start changing up the context from something simple to more advanced with additional filters etc.

See below

It’s very likely you need to use something different to ALLSELECTED to get this working correctly.

Zoom though the above workshop I’m confident you’ll have a much better idea after it as it covered RANKX in a lot of detail

Thanks
Sam