cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

 

2017-08-24 16_13_05-RSWA Data.xlsx - Excel.png

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Top N + Other

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

Capture.PNG

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors