Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have created a Power BI table which ranks the top 10 customers by sales. What now I need to do is take all of the remaining customers and combine them into one customer named "Other" and post it at the bottom of the table (as shown below). What is the best way to accomplish this?
Rank | Customer | Sales |
1 | Customer 6 | 15,531 |
2 | Customer B | 13,658 |
3 | Customer X | 9,158 |
4 | Customer 1 | 9,075 |
5 | Customer 3 | 8,245 |
6 | Customer A | 6,428 |
7 | Customer 9 | 3,127 |
8 | Customer 7 | 3,001 |
9 | Customer 2 | 2,854 |
10 | Customer Z | 1,024 |
| Other | 10,336 |
Solved! Go to Solution.
@Anonymous
In this scenario, I think you can firstly create a calculated column for RANK:
RANK= RANKX(ALL(Table), SUMX(Table, Table[Sales]))
Then create a display name column based on this RANK column:
DISPLAY_CUSTOMER= IF(Table[Rank]>10,"Other",Table[Customer])
Now you just need to drag the DISPLAY_CUSTOMER column into your table visual, all the "Other"s will be aggregated.
Regards,
@v-sihou-msft I was thinking about that, the only reason that solution would not work is if the Top 10 needs to be dynamic ie. respond to filters. If filters don't really matter, then that solution is great.
Here's a template for TopN & Other I've been playing with:
https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=1
The final measure is [Sales Amount Top & Other] which is displayed per Customer for Top Customers, otherwise just totalled.
I also threw in a Rank measure.
It might not fit everyone's requirements, but just another idea to throw into the mix 😉
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |