How to group rows with first N and other

[Filippomonti]

Hello,

i'm facing a problem with a report of quantities sales.

I have 2 levels of row like this

- Nation 1

     - Customer 1

     - Customer 2

     - Customer N

- Nation N

And i need to group only the customer displaying all the nations. So the only level that have to change is customer to show the report (matrix) like this:

- Nation 1

   - Customer 1

   - Customer 2

   - Customer 3

   - Customer 4

   - Customer 5

   - Other

- Nation 2

   - Customer 1

   - Customer 2

   - Customer 3

   - Customer 4

   - Customer 5

   - Other

- Nation N

   - Customer 1

   - Customer 2

   - Customer 3

   - Customer 4

   - Customer 5

   - Other

I want to display automatically only the top 5 customer per sales and the others in a generic "Other" section. If i use the group function I have to do that manually but that's painfully long with lots of customers for different nations.

How can I solve this problem?

Thanks in advance

[Samarth_18 · Accepted solution]

Hi @Filippomonti ,

Since there is no sample data provided in the question so I can't write the code directly.However,you can refer below article for getting TopN based on the nation. It helped me and I hope it will help you as well.

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Thanks,

Samarth

[Filippomonti]

Hi @Samarth_18 ,

Thank you very much for the support, I watched the video and it solved my problem.

Now, is there a way to alway display the "Others" row? I need to filter the matrix so i don't see rows with Sales Amount (as the video) = 0 but i need to see always Other.

Thank you in advance, best regards

Filippo

[Samarth_18]

Is it possible for you to share your PBIX after removing confidential data?

[Filippomonti]

I figured out how to do that and unfortunately I can't share my pbix. Anyway, thank you for your kind help!

Have a nice day 🙂