Top N and an Others bucket

DAX DAX Calculations
1

I’m looking to use Top N to have a dynamic bar chart that shows the Top 10 locations at my business and then lumps all others into an “Others” bucket. I walked through some of the other Top N solutions but I don’t think they quite applied. I need to still show Location Name for the Top 10 locations, but then have an 11th bar that shows “Others”. All the solutions I’ve seen seem to point to another table that might hold the category rankings but that won’t work for me as I need to actually show the location name along with that extra “Others” row.

The other twist I have is that they want to show 1 measure in the bar chart but have the Top 10 be based on another measure. So the end result would show a bar chart with 11 bars…Top 10 locations based on a Case Count Measure, but the measure actually shown in the bar chart is a different one, in this case a % Rate achieved.

I’m not sure if this is quite possible or not. Appreciate any help!

2

Please post a sample PBIX file so that we can better assist. Probably going to use the RANKX function instead of the TOPN.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

i’ve worked on something like this before. It can be done, but it’s not as easy as you would think. Here’s the outline of what I have done. There are notes in the code, but hopefully will be able to follow along:

/*These measure will take the TopN of a dimension ( City in this case) and return the top N individual valuesl
//along with a new row called "Remaining" with the remaing total
//Then a rankx measure will sort the cities from highest to lowest with the Remaining row always at the bottom


1.  Create a base measure
Total Sales = SUMX( 'Fact Sale', 'Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )

2.  Capture the selected value form a disconnected table for the Top N value 
Top Selection = SELECTEDVALUE( 'Top N Selection'[TopN Selection] )

3.  Create a calculated table that will be a union of the dist

3. Measure that does the heavy lifting
*/

Total Sales of Selected and Grouped Remaning = 

//Create a virtual table of the TopN of Cities 
VAR __TOPnTable=
	CALCULATETABLE(
			TOPN( 
				[Top Selection], 
				VALUES( 'Dimension City'[City]),
				[Total Sales],
				DESC
			)
	)


//Need to find all the cities not in the Top N, and get those cities total sales
VAR __RemainingTotal =
	CALCULATE( [Total Sales], EXCEPT( VALUES('Dimension City'[City]), __TOPnTable))

//Need to add a new row to the TopNTable, and can only do that via Row function
//be sure the first input has the same name as the column we are adding too
VAR __Remaining =
	ROW( "City", "Remaining")

VAR __Union = Union( __TopnTable, __Remaining)


RETURN
SUMX(
	INTERSECT( 'Remaining', __Union),
	
	VAR __CurrentRow = 'Remaining'[City]
	Return
	
	IF ( 
		__CurrentRow = "Remaining",
		__RemainingTotal, 
		CALCULATE( 
			[Total Sales], 
			'Dimension City'[City] = __CurrentRow
	
		)
	)
)

Enterprise%20DNA%20Expert%20-%20Small

1 Like
4

Please post a sample file, there is not a easy way to solve this without seeing how you have your data model setup.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

5

I will try and mock one up for you soon…I need to strip out a lot of the junk. I tried what Nick_M did above or something close. Biggest issues is dealing with 2 measures instead of just one. So I need to display 1 measure but rank by the others. Thanks for all your help.