TopN + Others

[Anonymous]

Hi Dear Community,

I have created this Table and measure in order to get Top 5 cases.

Top casestable:

Top Cases2 = filter(UNION(DISTINCT('Cases'[L1_SubjectDesc]), DATATABLE("L1_SubjectDesc",STRING, {{"Others"}})), not ISBLANK('Cases'[L1_SubjectDesc]))

I also created 1:* relationship between this new table and Cases table.

And measure to sum top 5 cases count and the rest of cases count as "Others" :

Top 5 cases2 =

var TopCaseTable = TOPN(5, ALLSELECTED('Top Cases2'), [Case Count])

var TopCaseCount = CALCULATE([Case Count], KEEPFILTERS(TopCaseTable))

var OtherCases = CALCULATE([Case Count], ALLSELECTED('Top Cases2')) - CALCULATE([Case Count], TopCaseTable)

var CurrentCase = SELECTEDVALUE('Top Cases2'[L1_SubjectDesc])

Return

IF(CurrentCase <> "Others",

TopCaseCount,

OtherCases)

The problem:

The measure seems to work on table (without adding date field though) but not on matrix. The 2 fields are from Top Cases2 table:

 And itdoesn't work on a stacked bar chrat. First the legend shows all the subjects instead of top 5, then it seems to not show the top 5 of the cases.

 with the legend, which is from the Top Cases2 table.

Will appreciate your help to figure this issue out.

Cheers!

[amitchandak]

@Anonymous , do you need TOPN with others

Refer video from curbal -https://www.youtube.com/watch?v=UAnylK9bm1I