Reply
DFPRO
Frequent Visitor
Partially syndicated - Outbound

RANX returning Multiple Ranks for Same Value

A seemingly simple rankx measure is not returning the values I would expect, and I have tried everything to figure out the issue. 

 

I have a table of Scores per Person, where each Person may have 1 or more Scores on a test. I have created a measure to average their scores: average(Table[Scores]). This works correctly. 

 

I then created a measure to rank the average scores per person: rankx(allselected(Table[Names]),[Average],,desc,Dense). This mostly works, with a few exceptions: 

 

In the first example, Person 1, 2, and 3 are all tied on Average Score but Person 1 and 2 are tied on ranking, while Person 3 is one rank lower than them. You can see that this is not a rounding error of the averages (to my knowledge, unless PBI is calculating it in a strange way). The only difference is the quantity of scores. Additionally, Person 6 and 7 have the same Average as well and have different ranks. 

Rank Mismatched.png

 

In this example, Person A and B have the same average score, but different quantities of scores. The individual raw Scores for each person doesn't seem to have any effect either, as they are different for each person. 

Rank Tied 4th.png

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

Syndicated - Outbound

@DFPRO I have seen in the past, it is usually because of the decimal numbers rounding, try using following code or I would recommend use new RANK function, but first try with this

 

rankx(allselected(Table[Names]),CURRENCY([Average]),,desc,Dense)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

Syndicated - Outbound

@DFPRO yes it goes up to 4 decimals, as I mentioned, better to use the RANK function and yes you can use it for measures, not sure where you get that this cannot be used for measures. You can absolutely use it!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

Syndicated - Outbound

@DFPRO I have seen in the past, it is usually because of the decimal numbers rounding, try using following code or I would recommend use new RANK function, but first try with this

 

rankx(allselected(Table[Names]),CURRENCY([Average]),,desc,Dense)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

DFPRO
Frequent Visitor

Syndicated - Outbound

@parry2k thank you for the recommendation. This solution works, however I have concern over the rounding. Does currency() round to the 5th decimal point? I worry that there may be instances where this causes issues, and would prefer to round to the 10th or higher decimal, for example. However, based on my current data, it works correctly. 

 

I thought that RANK() could not be used to rank measures, only columns? In this case, I cannot use RANK(), but only RANKX(). 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)