Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
hi @rizkus
Your dataset is insufficient to reflect, not to mention, solve your problem. Consider expand it.
Any other suggestions/ input guys? much appreciated
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
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!
Hi @rizkus,
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,
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).
4. Now you also want to Filter Records with other Columns like "Type" which also can be acheived by the Range Slicer from 1 to N
5. Filter Records with other Columns like "Type" and “Year” to get result as shown in the below screenshot,
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |