Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.