Forum Discussion
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.
3 Replies
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().
