cancel
Showing results for
Did you mean:

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

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
Employee

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

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.

Super User

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 😉

Owen Auger
Blog
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:

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.

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

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.

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.

Employee

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

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

Frequent Visitor

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!

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.

Super User

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 😉

Owen Auger
Blog
Helper IV

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

Super User

@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
Blog
Helper IV

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

The problem is when i remove a month :

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]
)``````

Helper IV

Hi @OwenAuger ,

do you think i could fix the issue ?

THanks,

Alex

Super User

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
Blog
Helper IV

hi @OwenAuger ,

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

One last question i promise ...

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

Super User

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
Blog

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors