Forum Discussion
Dynamic RANKX with Slicer and 2 Groups
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 |
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.
5 Replies
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-columnsHi 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:# Ranking =RANKX(FILTER(Machine_Data_Agg, Machine_Data_Agg[job_name] = MIN(Machine_Data_Agg[job_name]) ),DIVIDE(CALCULATE(SUM(Machine_Data_Agg[total_item_input])),CALCULATE((SUM(Machine_Data_Agg[total_seconds_idle]) +SUM(Machine_Data_Agg[total_seconds_jammed]) +SUM(Machine_Data_Agg[total_seconds_running]))/60)),,ASC,Dense)
Image of Current Outcome:
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.
