Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Top and Bottom 5 with RANKX based on other measures

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... 😞

 

DAX
RANKX issue
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , 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]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 checkedtotal over pickingtop 5 (ranking)
      108102
      166101
      553
      3045
      33744

       

       

       

      You know how I can do this? 🙂

      Thanks!

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

        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