cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CamIAm88
Regular Visitor

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_TypeItems_Worked Hours_Worked Items/MinuteRank
A8511.46
B11220.96
C21213.53

 

1 ACCEPTED 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.

 

 

View solution in original post

5 REPLIES 5
v-yinliw-msft
Community Support
Community Support

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:

vyinliwmsft_0-1669174096793.png

vyinliwmsft_1-1669174108987.png

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.

 

 

amitchandak
Super User
Super User

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

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

CamIAm88_0-1669235193629.png

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.