Reverse Ranking DAX or Measure

[Anonymous]

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!

[Anonymous]

@mahoneypat Thank you! That was the missing piece!

[mahoneypat]

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

[Anonymous]

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!

[mahoneypat · Accepted solution]

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