Forum Discussion
RANKX Returning False Ties
Having a bit of an issue with the RANKX feature. I'm trying to rank a listing of customers by total revenue. Effectively, the rank function is returning false ties and I can't figure out why/how this is possibly happening.
Below you will find the functions I use for Total Client Rev and the Client Rank.... Any help would be greatly appreciated..
Total Client Rev = SUM('Client Data'[Row Net Revenue Excluding Reserves - Last 12 trailing months (TTM) ])
Client Rank = RANKX(ALL('Client Data'),[Total Client Rev],,DESC)
The problem appears to be that the Client Rank measure is using the rows of the 'Client Data' table as a basis for the ranking, rather than the distinct Customer Codes.
The result is that any client whose [Total Client Rev] is at least as large as the largest [Total Client Rev] revenue evaluated in the row context of any individual row of 'Client Data' will be ranked 1, and so on.
If you adjust your measure as follows, it should give you sensible ranks:
Client Rank = RANKX ( ALL ( 'Client Data'[Customer Code] ), [Total Client Rev],, DESC )
Regards,
Owen
5 Replies
- OwenAuger
Super User
The problem appears to be that the Client Rank measure is using the rows of the 'Client Data' table as a basis for the ranking, rather than the distinct Customer Codes.
The result is that any client whose [Total Client Rev] is at least as large as the largest [Total Client Rev] revenue evaluated in the row context of any individual row of 'Client Data' will be ranked 1, and so on.
If you adjust your measure as follows, it should give you sensible ranks:
Client Rank = RANKX ( ALL ( 'Client Data'[Customer Code] ), [Total Client Rev],, DESC )
Regards,
Owen
This worked perfectly. Thanks so much. I'll keep in mind that I need to be column specific for these RANKX files.
bump.
v-piga-msftResident Rockstar
Hi dsforman5,
I have made a test with Rankx, you could refer to the formula below.
Column 2 = RANKX(ALLSELECTED('Table1'[Sales]),'Table1'[Sales],,ASC)In additon, you could have a good look at this bolg Use of RANKX in Power BI measures which may help you.
If you still need help, please share some data sample which could reproduce your sceanrio and your expected output.
Best Regards,
Cherry
In this scenario the problem is well explained how it comes from the structure of the data and what is used as parameters in the RANKX function.
In a different scenario I encountered a different possible cause: Rounding error.
When the calcualton is done in the second parameter of RANKX and then it is done again in the third (or blank) parameter for comparison, there can be a rounding difference, so that the rank is not matched exactly and the neighboring rank is returned resulting in wrong ties, and also ties that seem not to comply with the ties setting in the fifth parameter in case it is set to "dense".The code that produces the wrong ranks has this structure:
RANKX(MyDimension[MyAttribute], [MyMeasure])The code that fixes the problem has this structure:
RANKX(MyDimension[MyAttribute], ROUND([MyMeasure], 4))
