Get the Next Top 20 companies

[Kiran_yed]

Hello

I need help getting the next 20 best companies using the Rankx feature. I've achieved this by using the next dax measure

RANKX(All(sampledata_forBI[Company],sampledata_forBI[Company_Category_Group]),[TotalStaff],,DESC)>20 && RANKX(All(sampledata_forBI[Company]),[TotalStaff],DESC)<-40))

But due to the duplicate classification I get more than 20 companies presented in a tabular chart. I just need the Next Top 20, not the First Top 20, but the next one. So this can be achieved by obtaining a unique ranking of companies by ranking the same personnel companies according to their alphabetical order.

Here I provide the sample data structure of mine:

Company staff
abc 2
efg
2
cda
5 abc
2
efg
2 cda 2 abc 2 efg 2
cda 5
abc 2
efg 2
cda 2
mg 8
pg 5

Output required in a virtual table

Company	Total staff	Rank,Desc	Departure required
Abc	4	4	4
Efg	4	4	5
Cda	14	1	1
Mg	8	2	2
Pg	5	3	3

Can someone please help me achieve this...

Thank you in advance,

Kiran

[FarhanAhmed]

Try below , you need to include Skip after DESC .

_Rank Dense = RANKX(ALL(sampledata_forBI[Company],sampledata_forBI[Company_Category_Group]),[TotalStaff],,DESC,Dense) //Will not skip generate next immediate number like 7 7 next number will be 8
_Rank Dense Filter = CALCULATE(IF(HASONEVALUE(sampledata_forBI[Company]),[_Rank Dense] ,BLANK()),FILTER(ALL(sampledata_forBI[Company],sampledata_forBI[Company_Category_Group]), [_Rank Dense]>=11 &&  [_Rank Dense]<=20))



_Rank Skip= RANKX(ALL(sampledata_forBI[Company],sampledata_forBI[Company_Category_Group]),[TotalStaff],,DESC,Skip) //Generate  new number after ties like 7 7 next number will be 9
_Rank Skip Filter = CALCULATE(IF(HASONEVALUE(sampledata_forBI[Company]),[_Rank Skip] ,BLANK()),FILTER(ALL(sampledata_forBI[Company],sampledata_forBI[Company_Category_Group]), [_Rank Skip]>=11 &&  [_Rank Skip]<=20))

[Kiran_yed]

Gives blank for all

[v-diye-msft]

Hi @Kiran_yed 

Please find my sample file attached. any elaboration based on it will be helpful. 🙂

[Kiran_yed]

Hi Dinna,

Seems like I do not have permissions to attach the file, could you please share me your mail address to share the sample file for further understanding.

[Kiran_yed]

Thanks for the file

[v-diye-msft]

Hi @Kiran_yed 

Did you try my suggestion? if the results is blank, could you plz kindly provide your dummy pbix that I can test it further?

[v-diye-msft]

Hi @Kiran_yed 

Try this one:

Measure 2 = var t = 
    SUMMARIZE (
        ALLSELECTED ( 'sampledata_forBI' ),
        'sampledata_forBI'[Company]
        
    ,"staff",SUM(sampledata_forBI[Staff]))
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER ([staff],SUM('sampledata_forBI'[Staff]),ASC,
                    sampledata_forBI[Company], SELECTEDVALUE ( sampledata_forBI[Company]), DESC

            )
        )
    )

[Kiran_yed]

No the measure just gives me one for all the rows

[PaulDBrown]

@Kiran_yed 

I'm not sure whether this will work, but it might be worth a try.

1) Include an index column based on the alphabetical order of each company

2) create a fake total by subtracting the total number of staff for each company  - Index / 100000 (to give you a remote fraction which In effect will differentiate totals for each company).

for example:

Company	Staff total	alphabetical index	fake staff total
Abc	4	1	3.999999999
Efg	4	2	3.999999998

and use this fake total to create the rank.

make sense?

Here is a PBIX example:

[amitchandak]

@Kiran_yed , You can use top N , Or rank refer these eammples

City Rank = RANKX(all(Geography[City]),[Sales])
Geography Rank = RANKX(all(Geography),[Sales])
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )

https://databear.com/power-bi-dax-topn-function/

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
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...