Forum Discussion
Reverse Ranking DAX or Measure
Hi Everyone!
I am trying to reverse rank in PowerBI Desktop (this will eventually get published online, if that matters).
The following is a sample data table which is very similar to what I am working with. I am only showing relevant columns here.
DateNameTime for action
| Data Set | ||
| Date | Name | Time (s) |
| 1/1/2022 | John Doe | 5 |
| 1/1/2022 | John Doe | 5 |
| 1/1/2022 | Jane Doe | 4 |
| 1/1/2022 | John Doe | 7 |
| 1/1/2022 | Jane Doe | 6 |
| 1/1/2022 | Mark Marlo | 8 |
| 1/1/2022 | Mark Marlo | 10 |
| 1/1/2022 | John Doe | 10 |
I would like to rank the people in the following manner:
DateNameTime for actionRank
| Ranked Table | |||
| Date | Name | Total Time (s) | Rank |
| 1/1/2022 | John Doe | 27 | 3 |
| 1/1/2022 | Jane Doe | 10 | 1 |
| 1/1/2022 | Mark Marlo | 18 | 2 |
This is a sample table. My actual data goes back 30 days. As you can see from the data table, there are multiple records for the same person.
I have watched a lot of YouTube videos for ranking top 10 products or bottom 10 products and tried to adapt it to my use case. Being a very new PowerBI user, I am at my wit's end on how to approach this.
If it matters, I am also filtering the data by "Today", "1 Week", "2 Weeks", "1 Month".
Appreciate any and all help!
Thank you!
Is the Operator column shown the same one you put in the ALLSELECTED? Is that column sorted by another column? Do you have multiple tables in your model? If so, what are the relationships between them?
Pat
4 Replies
Please try this measure expression in a table visual without your Date column (you mentioned using date filters over a bigger date range). Just replace T1 with your actual table name.
ReverseRank =
RANKX( ALLSELECTED( T1[Name] ), CALCULATE( SUM( T1[Time (s)] ) ),, ASC )Pat
Hi mahoneypat !
Thank you so much for your quick response!
Your DAX did get me some results. I am seeing that the ranking does not change with selection.
I guess, I need to provide more context. My bad.
During any shift, I can have anywhere between 9-12 operators working. So, based on the timeframe, I would see multiple instances of the same operator. The time field is capturing the time it takes them to complete an action. For example, in one shift I may have 10 operators working. They are performing the same action repeatedly - which is picking an item, scanning it, and placing it in a bin. They maybe picking around 5,000 items. So during one shift, I may have around 50,000 datapoints of time. What I want to achieve is that depending on the timeframe selected, I want to rank the performance of each operator from 1 to total number of operators working during that timeframe.
The result I am seeing ranks each action by an operator, I guess. I see the rankings in 32,000 range. I am not able to see the ranks from 1 to N. Please see the screenshot below:
If I add "Dense" at the end of your measure, the rankings change to 700 series.
Can you please advise?
Thank you!
Is the Operator column shown the same one you put in the ALLSELECTED? Is that column sorted by another column? Do you have multiple tables in your model? If so, what are the relationships between them?
Pat
mahoneypat Thank you! That was the missing piece!