Dynamic top 10 & others

[itsmeanuj]

Hello,

Is there a way where we can have a list of the top 10 values (based on criteria) and the rest of the values can be clubbed as "Others" at 11th position? This needs to be dynamic as 10 top values may change with more data pouring in.  I have created a sample matrix visual (see below) in the PBIX file placed here https://drive.google.com/file/d/17g5I_FCuchng8AtxevOQ9f9ncklJMnWu/view?usp=sharing

Thanks,

Anuj

[johnyip]

@itsmeanuj , you can create a calculated table as follows.

CalculatedTable = 
VAR StagingTable_a = SUMMARIZE(FILTER(ALLSELECTED(Sheet3),Sheet3[DENIAL_REASON]<>BLANK()),
                             [DENIAL_REASON],
                             "Count",COUNT(Sheet3[PATIENT_NUMBER])
)
VAR StagingTable_b = ADDCOLUMNS(StagingTable_a,"Rank",RANKX(StagingTable_a,[Count],,DESC,Dense))

VAR Top10_reason = SELECTCOLUMNS(FILTER(StagingTable_b,[Rank]<=10),"DENIAL_REASON",[DENIAL_REASON])

VAR Top10_table = FILTER(StagingTable_a,[DENIAL_REASON] IN Top10_reason)
VAR Others_table_staging = SELECTCOLUMNS(ADDCOLUMNS(FILTER(StagingTable_a,NOT([DENIAL_REASON] IN Top10_reason)),"Others","Others"),"DENIAL_REASON",[Others],"Count",[Count])
VAR Others_table = SUMMARIZE(Others_table_staging,[DENIAL_REASON],"Count",SUMX(Others_table_staging,[Count]))
VAR Result = UNION(Top10_table,Others_table)
RETURN
Result

And then use the Count column in the calculated table in your table vis, aggregraed using sum. The same applies for %GT but also show value as percentage of grand total.

[mlsx4]

Hi @itsmeanuj 

This video has a good explanation on it: https://www.youtube.com/watch?v=JLK2e0k8OBE

Also this link: https://www.proserveit.com/blog/ms-power-bi-topn-and-other