topic Top and Bottom 5 with RANKX based on other measures in DAX Commands and Tips

Top and Bottom 5 with RANKX based on other measures

Anonymous — Wed, 04 May 2022 12:32:22 GMT

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

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

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

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

Re: Top and Bottom 5 with RANKX based on other measures

amitchandak — Thu, 05 May 2022 04:24:16 GMT

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]))

Re: Top and Bottom 5 with RANKX based on other measures

Anonymous — Thu, 05 May 2022 15:21:57 GMT

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!

Re: Top and Bottom 5 with RANKX based on other measures

v-easonf-msft — Tue, 10 May 2022 08:55:33 GMT

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

Re: Top and Bottom 5 with RANKX based on other measures

Anonymous — Tue, 10 May 2022 13:02:53 GMT

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