Forum Discussion

itsmeanuj's avatar
itsmeanuj
Icon for Helper IV rankHelper IV
3 years ago

Dynamic top 10 & others

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 ...
Need Help
Show and Tell
Tips and Tricks
johnyip's avatar
johnyip
Icon for Solution Sage rankSolution Sage
3 years ago

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.