Top N + Other

[olimilo]

Using the following DAX code to get the Top N + Other, but this seems to fail when I have a Page-level filter applied. Is there any way I can get the desired output with filters on?

Top Violations = 
IF (
    CONTAINS (
        TOPN (
            10,
            SUMMARIZE(
				'Violations',
				'Violations'[Violation], 
				"x", 
				COUNTROWS('Violations') + 0
			),
            [x]
        ),
        'Violations'[Violation],
		'Violations'[Violation]
    ),
    'Violations'[Violation],
    "All other violations"
)

For example, if I had the following data:

ViolationSection	Violation	NoOfViolations
C	CE	88
A	AE	84
B	BE	76
C	CA	48
C	CB	47
A	AA	37
B	BB	33
C	CC	24
C	CD	23
A	AD	22
B	BD	21
A	AC	20
A	AB	19
B	BC	18
B	BA	17

And then tried to get the Top 3 for ViolationSection B, the code above will only output BE and then place everything else under others (right chart). The intended output is BE, BB, BD and Others (left chart).

[Eric_Zhang]

@olimilo

You may create two calculated columns

rank = RANKX (
 FILTER( Table1,Table1[ViolationSection]=EARLIER(Table1[ViolationSection])),
CALCULATE(SUM(Table1[NoOfViolations]),ALLEXCEPT(Table1,Table1[ViolationSection],Table1[Violation])),
,
DESC
)

violation_ = IF(Table1[rank]>3, "Other",Table1[Violation])