topic Re: show top n subcategory (without filtering) in DAX Commands and Tips

show top n subcategory (without filtering)

Anonymous — Wed, 18 May 2022 19:11:57 GMT

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.

Re: show top n subcategory (without filtering)

amitchandak — Thu, 19 May 2022 01:25:43 GMT

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

Re: show top n subcategory (without filtering)

Anonymous — Thu, 19 May 2022 21:07:08 GMT

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.

Re: show top n subcategory (without filtering)

Anonymous — Tue, 24 May 2022 09:39:09 GMT

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

Re: show top n subcategory (without filtering)

Anonymous — Tue, 24 May 2022 20:40:41 GMT

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"

Re: show top n subcategory (without filtering)

Anonymous — Wed, 25 May 2022 01:58:14 GMT

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