- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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().
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
05-22-2024 01:44 AM | |||
08-26-2024 09:19 PM | |||
04-18-2024 09:06 AM | |||
12-12-2023 11:11 AM | |||
05-03-2024 12:16 PM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
52 |