Dynamic Top 10 + Others

Question

Hi guys, i need some help regarding creating a Top10 customers plus others.

Customer	Amount	Type	Year
A	100000	Farm	2020
B	200000	Fresh	2021
C	300000	Mall	2022

I want to create a column chart where i can show just the Top 10 customers with the highest amount, and the rest should be classified as others. I want it also to when i create the Type and Year slicer, it dynamically shows the Top 10 + Others based on the filter i applied to aswell.

Thank you so much for the help, really appreciate any input on this!

rizkus · Answer

I have tried creating a rankx DAX formula as follows (My table name is "ALL")

Column =

Var A = RANKX(ALL(ALL, [Amount] , , DESC)

return

IF(A <= 10, ALL[Customer], "Others")

However, that only return the top 10 of all and cant be dynamically seen (if i put in the filter of Type = Farm, or Year = 2020)

Any suggestion is appreciated, thanks!

saminogic · Answer

Hi rizkus,&nbsp;In order to have the data to be filtered as Top N Customer with Highest to lowest Amount from table in Power BI, you can follow below steps:1. In power query sort the "Amount" column in Descending order, now create an index column as follows in this screenshot,&nbsp; &nbsp; &nbsp;&nbsp;2. This option will add a Index starting from 1 for all record based on descending order with Amount column. Please refer to the below screenshot for the same.3. Now this index column can be used as a Slicer Visual as shown in the below screenshot. Consider we add the max Slicer Number is 11 as the Record Count is 10. (in this slicer you can add any number to find Top N records).&nbsp;4. Now you also want&nbsp;to Filter Records with other Columns like "Type" which also can be acheived by the Range Slicer from 1 to N5. Filter Records with other Columns like "Type" and “Year” to get result as shown in the below screenshot,&nbsp;If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.Thanks!Inogic Professional Service DivisionAn expert technical extension for your techno-functional business needsPower Platform/Dynamics 365 CRMDrop an email at crm@inogic.comService:&nbsp; http://www.inogic.com/services/&nbsp;Power Platform/Dynamics 365 CRM Tips and Tricks:&nbsp; http://www.inogic.com/blog/

rizkus · Answer

Hi, thank you for the answers, but this doesnt solve the "others" part? preferably, since the data i have may contain the same customer name along different type and year. and how would this pan out in DAX? thank you