Forum Discussion
Distinct Rank for each criteria
This is difficult since the built in RANKX function does not have an option to handle ties this way. It would reutrn ties with non distinct numbers and would have similar values to your Actual Rank column.
One way to get around this limitation is to modify the Score data so that all values are unique. This can be done by adding a very small random number to the score data. The number would be so small that it would not change the score in any meaningful way but would allow the numbers to be unique so that ties are handled correctly when using the RANX function.
I would add a calculated column called Score Random:
Score Random = (RAND()*0.000001) + 'Table'[Score]
Then add a column that ranks based on the Score Random column:
Rank = RANKX(FILTER('Table', 'Table'[Employee Code] = EARLIER('Table'[Employee Code])), 'Table'[Score Rand],,DESC,Skip)I ended up with this table as a result:
This is difficult since the built in RANKX function does not have an option to handle ties this way. It would reutrn ties with non distinct numbers and would have similar values to your Actual Rank column.
One way to get around this limitation is to modify the Score data so that all values are unique. This can be done by adding a very small random number to the score data. The number would be so small that it would not change the score in any meaningful way but would allow the numbers to be unique so that ties are handled correctly when using the RANX function.
I would add a calculated column called Score Random:
Score Random = (RAND()*0.000001) + 'Table'[Score]
Then add a column that ranks based on the Score Random column:
Rank = RANKX(FILTER('Table', 'Table'[Employee Code] = EARLIER('Table'[Employee Code])), 'Table'[Score Rand],,DESC,Skip)
I ended up with this table as a result:
Yeah it worked and meet my expectation. Thanks a lot!!
Hi - think this is my first post!
Is there a way to expand this to two criteria? I have a list of reasons that repeat for different customers in the same table, so the ranking in my model needs to be rank by customer by reason by number of reasons
hope this makes sense, can provide sample data if needed!
Thank you :)
Steve
How would you get this rank to skip all instances of lower values. i.e. display only the rank for the highest score for each employee?
