Show Top N and sum up the Others as a value

[pshah]

I created a Rank column and a custom measure. I've filtered on Rank to show less than 11, so basically show me the top 10.

The custom measure is basically saying to show me when rank <= 5, then show Amount else Others.

Instead of "Others", is there a way to sum up the Amount value and end up only showing 6 rows. First 5 rows shows the top 5 countries, and then the 6th row sums up the countries that are ranked from 6 to 10.

[Phil_Seamark]

HI @pshah

Is your City Rank a calculated measure or a calculated column?  Can you please share the code you used for this.

For the 6th row, what you like to see in the 2nd and 3rd columns?

[pshah]

Calculated measure -> Ctry Rank = RANKX(ALL('Dim - Country'[Country Name]), 'Dim - Country'[Total])

For the 6th row, I'd like to see "Others" in the 2nd and 3rd column, if that makes sense.

[pshah]

For the 4th column, I'd like to show the remaining amount on the 6th row.

Based on the screen shot, the math will come out to a little under 50m. So I'd like to show that amount / value in the 4th column and 6th row.

[parry2k · Accepted solution]

Here is the solution:

1. Goto modelling tab and click new table and add following dax :

Country with other = UNION(Values('Dim - Country'[Country Name]), ROW("Country Name", "Others"))

it will create a new table with all the countries and addition country "Others"

2. Set relationship with this new country with your original table (it will be 1 to many)

3. change your country rank measure like this 

Ctry Rank = RANKX(ALL('Country with others'[Country Name]), 'Dim - Country'[Total])

4. Add new measure "Total with other" -> This is final measure we need

Total with other = 
if([Ctry Rank] <= 10, 'Dim - Country'[Total],
	if(HASONEVALUE('Country with others'[Country Name]), 
		if(values('Country with others'[Country Name]) = "Others", 
			sumx(filter(all('Country with others'[Country Name]), [Ctry Rank ] > 10),  'Dim - Country'[Total])
		)
	)
)

5. Add new table visual , drop country name from "Country with other table" and Total with other measure

I expect that you will top 10 countries with their value and rest should be all summed up with "Others"

PS - There could be typo in DAX expression, let me know if it doesn't work

[pshah]

Thank you!! I'll try this, currently in DirectQuery mode so will have to Import. 

Will let you know if it worked or not.

[parry2k]

Sounds good. 

[pshah]

Sorry for the very late reply but it worked! Thank you very much for the help!!!!

[barrybilewitz]

I adapted your formulae for media sales and it worked like a charm - thank you.

[parry2k]

@barrybilewitz glad to hear