Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago

Top N + Other

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:

 

ViolationSectionViolationNoOfViolations
CCE88
AAE84
BBE76
CCA48
CCB47
AAA37
BBB33
CCC24
CCD23
AAD22
BBD21
AAC20
AAB19
BBC18
BBA17


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

 

Need Help

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

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