cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ganapathi
Frequent Visitor

Distinct Rank for each criteria

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.

 

Distinct Rank.JPG

1 ACCEPTED SOLUTION
Twan
Advocate IV
Advocate IV

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:

 

Rank Distinct.PNG

View solution in original post

5 REPLIES 5
Twan
Advocate IV
Advocate IV

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:

 

Rank Distinct.PNG

View solution in original post

Anonymous
Not applicable

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
Regular Visitor

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
Frequent Visitor

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

KHorseman
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.