Top 10 / Other

[Anonymous]

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

[v-sihou-msft · Accepted 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,

[jahida · Accepted solution]

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

[Anonymous]

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:

Thank you in Advance.

[NerdFlanders]

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]

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]

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]

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]

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.

[v-sihou-msft · Accepted 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,

[Anonymous]

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]

@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

[Rob83]

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]

Excellent Feedback...Thanks!

[jahida · Accepted solution]

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

[OwenAuger · Accepted solution]

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 😉

[alexbalazsalex]

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

[OwenAuger]

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

[alexbalazsalex]

Hi @OwenAuger 

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

Thanks for your support

[alexbalazsalex]

Hi @OwenAuger ,

do you think i could fix the issue ?

THanks,

Alex

[OwenAuger]

@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

[alexbalazsalex]

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

[OwenAuger]

@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