Forum Discussion
Dynamic Top 10 + Others
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
