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

Need Help
Show and Tell
Tips and Tricks

2 Replies

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