Top and Bottom 5 with RANKX based on other measures

[JulieB_]

Hi all, 

I need to calculate a top and bottom 5 employees based on the result of two measures. 

I tried to use RANKX but the problem is that I need to take into account two measures. Like a double ranking...

Below 3 examples:

'Collis checked' and 'Total Over Picking' are measures

1)  2)  3)

1) Show the BOTTOM 5 'GOLD numbers bereider' with the most 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 294 2. 311 3. 450 4. 844 5. blank 2) Show the TOP 5 'GOLD numbers bereider' with the less 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 065 2. 431 3. 942 4. 248 5. 816 3) Show the TOP 5 'GOLD numbers bereider' with the less 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 696 2. 487 3. 140 4. 051 5. 722

I created a test powerbi file with sample data. You can download the test file via this link in WeTransfer: https://we.tl/t-tr6d0dzIQH

Hope someone can help out. I already broke my head hours on this one... 😞

[amitchandak]

@JulieB_ , File seem to have some different data. you need try like

top and bottom 5 = CALCULATE([Total Over picking],TOPN(5,ALLSELECTED(Data[CODPRE]),[Total Over picking],DESC),VALUES(Data[CODPRE]))+ CALCULATE([Total Over picking],TOPN(5,ALLSELECTED(Data[CODPRE]),[Total Over picking],ASC),VALUES(Data[CODPRE]))

[JulieB_]

hi @amitchandak , your proposed solution is not exactly what I try to obtain

Your 'top and bottom 5' returns the value of the 'total over picking'. I would like it to return a ranking (number 1 to 5). Also if the top 5 of the 'total over picking' contains several times the same value then it should look at the 'collis checked' measure to rank those values.  In the left example: top 5 result would be  collis checked total over picking top 5 (ranking) 108 10 2 166 10 1 5 5 3 30 4 5 337 4 4

You know how I can do this? 🙂

Thanks!

[v-easonf-msft · Accepted solution]

Hi, @JulieB_ 

For your last requirement, please try formula as below:

Ranking = 
RANKX (
    ALLSELECTED( Data[CODPRE] ),
    RANKX ( ALLSELECTED( Data[CODPRE] ), [Total Over picking],,DESC )
        + DIVIDE (
            RANKX ( ALLSELECTED( Data[CODPRE] ), [Collis checked],,DESC ),
            COUNTROWS ( ALLSELECTED( Data[CODPRE] ) ) + 1
        )
        ,,
    ASC,
    DENSE
)

Ranking(<5) = 
IF([Ranking]<=5,[Ranking],BLANK())

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[JulieB_]

Thank you @v-easonf-msft! That measure is exactly what I needed!
Would never have been able to figure that out all by myself 😳