cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ganapathi Frequent Visitor
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

Accepted Solutions
Twan Regular Visitor
Regular Visitor

Re: 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:

 

Rank Distinct.PNG

View solution in original post

4 REPLIES 4
KHorseman Super Contributor
Super Contributor

Re: Distinct Rank for each criteria

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? Please mark it as a solution.

Proud to be a Datanaut!
Twan Regular Visitor
Regular Visitor

Re: 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:

 

Rank Distinct.PNG

View solution in original post

Ganapathi Frequent Visitor
Frequent Visitor

Re: Distinct Rank for each criteria

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

Highlighted
spjmorris Visitor
Visitor

Re: Distinct Rank for each criteria

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 281 members 2,792 guests
Please welcome our newest community members: