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