Top N and an Others bucket

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