PowerBI - Dynamic TopN + Others depending on slicer

Question

I have data structured like this

Date, Group, Value
1/1/2020, A, 10
2/1/2020, A, 5
3/1/2020, A, 7
1/1/2020, B, 1
2/1/2020, B, 3
3/1/2020, B, 7
1/1/2020, C, 1
2/1/2020, C, 3
3/1/2020, C, 7

With ~20 Groups. I want to create Pie/Tree chart, to show f.e. Top 5 Values and the rest as OTHER. It's pretty simple to do once, but what if I have slicer that filters the graph by Years or Months, accuired from Dates Table connected to Date column? So when I check only 2020, it would show Top N + Others for 2020 only, and not all data? Only TopN in graph filters isn't enough, since it sums values on graph to 100%, and I want to see what's the real percentage in terms of all Groups.

Monstah Monstah · Accepted Answer · 2020-07-17T21:18:25

Tangent: I wrote a lengthy reply telling you how to make a proper measure that would return the correct percentage, and in the end realized TopN would make it sum up to 100% anyway. So, always keep that in mind: if you pay attention, you'll learn even when you teach ;) Anyway,

What I would try then, is to have a specific measure, such as

[Value % (top N)] = 
var denominator = CALCULATE ( [Value], ALLSELECTED ( Data_table ))
var rank = RANKX ( VALUES ( Data_Table[Group] ), [Value] ))
return IF ( rank <= *N*, DIVIDE ( [Value], denominator ))

and then, remove the TOPN filter from the visual. For groups which are not in the top N, the measure will return blank, so they won't show on a visual.

If, however, you want to show the "Others" line, with the rest of the sum, then you need to work a little more. For one thing, you need to have a line with "Others" for the group, on the same table as the actual groups you're summing, and the measure will need more branching logic to take that into account.

But the basic logic is still the same, using RANKX over the values in the Group column, then returning blank for most items (IF() by default returns a blank if the condition is false, but you can always have the measure return BLANK() yourself, in some edge cases).

PowerBI - Dynamic TopN + Others depending on slicer

1 Answers