Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

3 ACCEPTED SOLUTIONS

@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,

 

 

View solution in original post

jahida
Impactful Individual
Impactful Individual

@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.

View solution in original post

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 😉

 

Capture.PNG


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

58 REPLIES 58

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.