Forum Discussion
Top 10 / Other
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 |
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 ;)
58 Replies
I do not know exackt what the best are doing,
but I think you have to put a title on the column "Rank" 11 or "Other".
Please feel free to send me the code how you got them 10-ranking "
Thanks
// Totte67Thanks for replying...
I created a measure "Top 10 Flag = if([Rank]>10,"Other","Top 10"). From there I filtered the table by the Top 10 Flag measure "is not Other" and than makes only Top 10 in ranking appear. I could have skipped this measure and just use a filter in Rank that filters out ranks greater than 10. The reason I created the Top 10 Flag measure was an attempt to write a another formula that looked at that result and created a customer column that lists the customer name as Other if "Other" appears in that field and the customer name if if did not. I could not come up with the next formula that properly did this. Perhaps there is a better way to do this...no clue.
Is Rank a column or a measure? Also does each row of this table represent 1 row in the base table or the aggregation of multiple rows?
When will this feature be implemented? We need it for the charts, it's a really important feature and there is no workaround to implement this in charts so far.
