How to show "Others" in a Matrix (pivot table)

[pshah]

I have a filter to show me top 10 country by Amount.

Say I want to change that to top 5, and show the other remaining countries as "Other" and sum up the Amount for all the countries outside of top 5, how would I do that?

[parry2k]

I know in other post from you we resolved how to show country name others , you need a summarize table, here is a post which talks about it

[pshah]

Can you create a new table in DirectQuery mode or do you have to do Import?

Also, I need to order the Country Names by the value instead of alphabetical order... any idea on how to do that?

I created a rank column and then concatenated the rank column and country name but I can't throw that under "Columns" in a Matrix as the column is a Measure.

[parry2k · Accepted solution]

I don;t think you can create table in DirectQuery mode, in that case you have to import.

Once you create summarize table, you can add rank as column (not measure) based on total sales and then sort country by this new rank column.

[pshah]

Ok, thank you!!!

[parry2k]

Few more posts

https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

https://stackoverflow.com/questions/38087699/power-bi-topn-and-all-other