Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I'm using a table to display metrics for all [Work_Type] for the employee and date range selected from a slicer. For example, if selecting Employee A and date range 1/2/2022 and 1/2/2022, the table would display a row for each [Work_Type] and KPIs such as SUM [Items_Worked], SUM [Hours_Worked], and a measure (Items/Min) that calculates the items worked per hour (i.e. DIVIDE([Items_Worked], [Hours_Worked])).
The trouble I'm having is showing a ranking. I've reviewed a few examples and see RANKX combined with CALCULATE can do the trick but I always seem to display rank 1 for all [Work_Type] rows. Ideally, I would like RANKX to evaluate the Items/Minute for each [Work_Type] and retrieve the Rank for all [Work_Type] for that employee.
Data Set from Table:
Date | Employee | Work_Type | Items_Worked | Hours_Worked | Items/Min |
1/1/2022 | A | A | 85 | 1 | 1.4 |
1/1/2022 | A | B | 112 | 2 | 2.6 |
1/1/2022 | A | C | 212 | 1 | 16.4 |
1/1/2022 | B | A | 315 | 2 | 3.0 |
1/1/2022 | B | B | 851 | 1 | 2.1 |
1/1/2022 | B | C | 165 | 2 | 7.2 |
1/1/2022 | C | A | 984 | 1 | 0.9 |
1/1/2022 | C | B | 684 | 2 | 14.2 |
1/1/2022 | C | C | 985 | 1 | 5.7 |
1/2/2022 | A | A | 354 | 2 | 1.1 |
1/2/2022 | A | B | 68 | 1 | 5.9 |
1/2/2022 | A | C | 984 | 2 | 3.6 |
1/2/2022 | B | A | 254 | 2 | 3.5 |
1/2/2022 | B | B | 351 | 1 | 1.4 |
1/2/2022 | B | C | 129 | 1 | 16.4 |
1/2/2022 | C | A | 865 | 2 | 8.2 |
1/2/2022 | C | B | 426 | 2 | 2.2 |
1/2/2022 | C | C | 311 | 2 | 2.6 |
Desired Results in Table:
- Employee Slicer Selection: A
- Date Slicer Selection: 1/1/2022
Work_Type | Items_Worked | Hours_Worked | Items/Minute | Rank |
A | 85 | 1 | 1.4 | 6 |
B | 112 | 2 | 0.9 | 6 |
C | 212 | 1 | 3.5 | 3 |
Solved! Go to Solution.
Hi @CamIAm88 ,
You can share the pbix file by onedrive then post the link or you can share the screenshot.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CamIAm88 ,
You can try this method:
New two columns:
Items/Minute = DIVIDE('Table'[ Items_Worked], 'Table'[ Hours_Worked] * 60)
Rank =
RANKX (
FILTER ( 'Table', 'Table'[ Work_Type] = EARLIER ( 'Table'[ Work_Type] ) ),
'Table'[Items/Minute]
)
The result is:
Hope these help you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your response Yinliw. I tried your approach but had to modify the Items/Minute calculation since the value will change depending on the timeframe selected by the slicer. I made some progress and now am obtaining rank values other than 1 but they are not accurate. Could you provide me with additional guidance? I've attached a PBIX file that might better illustrate my goal. The PBIX has two tabs, the first tab is where I'm trying to populate the rank and the 2nd tab provides the results. Ideally, the employee with the highest items/minute will be ranked 1, and so on. Do you know how I can best share this file with you?
Hi @CamIAm88 ,
You can share the pbix file by onedrive then post the link or you can share the screenshot.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@CamIAm88 , why rank is 6,6, 3
Rank can be done like. assume [Hour Worked] is measure in which you want rank
Rankx(All(Table[Work_Type]) , [Hour Worked], ,desc,dense)
or
Rankx(Allselected(Table[Work_Type]) , [Hour Worked], ,desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
Hi Amit - please allow me to elaborate.
I have a dashboard tab with two slicers (employee and date). This interacts with a table that displays certain KPIs. I want to rank according to items per minute which are shown in the column '# Ranking_Calculate'. The same formula is being used for # Ranking_Calculate as is being used in the RANKX formula.
Ranking Formula:
Image of Current Outcome:
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |