cancel
Showing results for
Did you mean:
Frequent Visitor

## Show Top N and sum up the Others as a value

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Show Top N and sum up the Others as a value

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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

13 REPLIES 13
MVP

## Re: Show Top N and sum up the Others as a value

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?

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Show Top N and sum up the Others as a value

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.

Frequent Visitor

## Re: Show Top N and sum up the Others as a value

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.

Super User

## Re: Show Top N and sum up the Others as a value

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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Show Top N and sum up the Others as a value

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

Will let you know if it worked or not.

Super User

## Re: Show Top N and sum up the Others as a value

Sounds good.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Show Top N and sum up the Others as a value

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

Frequent Visitor

## Re: Show Top N and sum up the Others as a value

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

Super User

## Re: Show Top N and sum up the Others as a value

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,224)