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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Anonymous
Not applicable

Hi, I am new in PowerBi. I saw you created rank table. Can you help me to get the output like you posted here.

 

My table is look like this and I want to filter the highest ranked value in my one colum. Please see the picgure below:

Highest Value.png

 

Thank you in Advance.

NerdFlanders
Helper I
Helper I

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.

Totte67
Frequent Visitor

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
// Totte67

Anonymous
Not applicable

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

jahida
Impactful Individual
Impactful Individual

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?

Anonymous
Not applicable

Rank is a Measure.  It has to be a measure because the data rows contain multiple sales results from each customer.  The table combines the results by customer and the RankX function assigns the order by combined sales.

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

 

 

Anonymous
Not applicable

I am trying to do this but I have ALLSELECTED instead of ALL in this first statement -

RANK=
RANKX(ALL(Table), SUMX(Table, Table[Sales]))

 That ranks as I would expect, but when I select the field I want in this second piece it errors with a circular dependency error.

DISPLAY_CUSTOMER=
IF(Table[Rank]>10,"Other",Table[Customer])

 I am trying to write this:

SourceURL_2 = If('Trading Email Clicks'[RANK] > 10,  "Other", 'Trading Email Clicks'[sourceurl])

 

Any help appreciated.

Anonymous
Not applicable

@v-sihou-msft would you be able to help with the post I placed? I am trying to use your solution to Top 10 / Other but it won't allow me to do the second element where it groups to 'Other' if rank > 10.

Thanks

Hi Simon_Hou-MSFT,

 

I'm trying to get a Pie Chart with the top 10 customers plus other with your DAX formula but with the Display_customer I get the next error:

 

"A single value for column 'customer name' in the table 'customer' cannot be determined. This can happen when a meassure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

How can I fix this?

Anonymous
Not applicable

Excellent Feedback...Thanks!

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.

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

hi @OwenAuger ,

 

Your template works for me until a certain point.

 

If i try to select only some months .. it is showing more customers than i select ...

 

Any Ideas why ?

 

Thanks,

Alex

@alexbalazsalex could you post an example of what you mean?

Perhaps an example of the output you are getting vs the output you expect, with some details on the data model.


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

Hi @OwenAuger 

 

Basicaaly for the full year (12 months) i have the below picture) which i sperfect for me :

 

Capture12.PNG

 

The problem is when i remove a month :

 

Capture13.PNG

 

As you can see is showing more than 6 .

 

Formulas are replciated from your file

Th Tn Top = CALCULATE (
    [Th Tn 2],
    KEEPFILTERS (
        TOPN ( [TopN Selection], ALL ( 'Table1'[Country] ), [Th Tn 2] )
    )
)

this is for Top

And the final formula:

Th Tn Top & Other = 
IF (
    HASONEVALUE ( 'Top & Other'[Top & Other] ),
    SWITCH (
        VALUES ( 'Top & Other'[Top & Other] ),
        "Top", [Th Tn Top],
        "Other", [Th Tn Other Total Only]
    ),
    [Th Tn 2]
)

 

Thanks for your support

Hi @OwenAuger ,

 

do you think i could fix the issue ?

 

THanks,

Alex

@alexbalazsalex 

On the face of it, I can't explain that behaviour.

Could you post a link to the PBIX behind the visuals you posted (santised data if needed)?

If that's tricky, I can try to reproduce with a dummy model at my end.

 

One thing - it would be best if Country and Month are in separate dimension tables if they aren't already.

 

Regards,

Owen


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

hi @OwenAuger ,

 

Thanks for you tip putting months in a different table fixed the issue...

 

One last question i promise ...

Capture auger.PNG

Your formulas are showing the top 10 countries of each year which is perfect ...

 

IS there a wat to not show blank for the ones that are in the matrix and considered top in different years ?

Basically not to leave any cells empty ?

 

thanks a lot for your support.

 

Alex

@alexbalazsalex 

That is an interesting requirement 🙂

Just confirming, what would you like to display instead of blanks? Just the usual [Th Tn 2] measure value for that country?

(Just wanted to check this before writing the DAX)

 

If so, that shouldn't be too tricky. It would effectively change the behaviour so that the "top" countries are countries that are in the Top N in at least one of the displayed years.

 

Regards

Owen

 


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.