Distinct Rank for each criteria

[Ganapathi]

Hi All,

i would like to create Distinct Rank for each employee code seperately based on score as shown in the below picture. Rank should be distinct. For Instance, if the score is same for two lines rank should not be 1 and 1, it should be 1 and 2.

[Twan · Accepted solution]

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:

[Anonymous]

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?

[spjmorris]

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

[Ganapathi]

Yeah it worked and meet my expectation. Thanks a lot!!

[KHorseman]

I'm not sure I understand how you intend for this to work. Why is each employee listed more than once with different scores, and what do you want to rank them against? It looks like you're ranking each score against the employee. Are you trying to rank each employee against every other employee? If so you're going to need to pick how you want to treat the multiple scores. Do you want the employee ranked based on their highest score only?  This looks like a confusing data model and I'm not sure it actually represents what you expect it to represent. What is your formula for that Actual Rank column?