show top n subcategory (without filtering)

[Anonymous]

Hello 

I have a question and I appreciate it if you help me. 

I have a matrix with 2 levels of category. the main category is the list of products and the subcategory is countries.

when drilling down to the second layer I want to not show all the countries and the value of the Measure I wrote for each of them. I want to show just the top 5 countries where the product is sold most, while these top 5 countries may differ for each product(main category).

I appreciate it if you help me.

just note that I don't want to filter the data based on the top 5 countries. I am trying to show the top 5 rather than filtering the whole table.

[Anonymous]

Hi @Anonymous ,

Are you want to show top 5 countries depend on different products?

Ie rank for countries group by products and show top 5?

If so create a measure like below and add it to visual filter and set value < 6.

Measure = RANKX(ALLEXCEPT('Table','Table'[product]),CALCULATE(SUM('Table'[sales])),,DESC,Dense)

If I misunderstood your meaning, please show some sample data and expected result.

Best Regards,

Jay

[Anonymous]

Hi @Anonymous 

back to your table. As it is shown in your table total sale is 16240. But in your second table the total sale is filtered based on top 5 countries in each group of product. 

What I want is to not filter the data; however display the top5 countries for each product and accumulate the rest countries as "others". in another word, to show six rows for each products, 5 rows for the top 5 countries and the last row is "other countries"

[Anonymous]

Hi @Anonymous ,

That's not supported I'm afraid. You cannot add extra rows to the matrix because this "other" does not exist in your table.

There is a way to make total and subtotal not affected by the filter, I can provide the formula if this is you want.

Best Regards,

Jay

[amitchandak]

@Anonymous ,

Try like

RANKX(all(Table[Country]), calculate(count(Table[Product]) ),,desc,dense)

Filter for <=5

Or use TOPN

calculate(count(Table[Product]) ,TOPN(1,all(Table[Country]),calculate(count(Table[Product]) )), values(Table[Country]))

TOPN https://youtu.be/QIVEFp-QiOk 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Anonymous]

@amitchandak 

Thanks for your response. As I emphasized in my question, I don't want to filter the data based on the top n=5 countries. As I put into practice your suggestion the data will be filtered based on the top n=5  countries; however, I want just top n =5 countries be displayed and the rest not be displayed , while they are not filtered.