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.

[alexbalazsalex]

hi @OwenAuger 

Exactly the usual sum of tons for that year ...show the all values if in one of the years in top 10 ..

Many Thanks for your support .

Alex

[alexbalazsalex]

Exactly the usual sum of tons for that year ...show the all values if in one of the countries in top 10 ..

[Matthias_13]

Thanks a lot @OwenAuger

what kind of Visualisation did you use for The final measure is [Sales Amount Top & Other] ? I tried many kind of them but the Grafik is not like your

[OwenAuger]

Hi @Matthias_13

I used the built-in Matrix visual when I first created this. However, the Matrix visual has been upgraded since then, and when you click on an "old" Matrix visual, you won't see any visual selected in the Visualizations pane. You can upgrade it to a new Matrix by clicking on the new Matrix icon.

With the new Matrix visual, you have more layout/formatting options and can produce basically the same layout as the old Matrix. For example, I set "Stepped layout" to Off to get a "tabular" layout (i.e. separate column per row header).

I have added a second tab to my sample file at the same link

https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=0

I have updated the above file with an "Old Matrix" and "New Matrix" tab, so you can compare the two.

Owen 🙂

[Matthias_13]

Hi @OwenAuger,

thanks a lot for your demonstration file. It helps me a lot! Now I can see which Visualization did you use. However in my Power BI ,i have tried to create the Table by myself but there is no detailed information about TopN Customers like yours, but only summary. Do you have any trick to make it better? or my Software is outdated?

 Thanks a lot for your help Owen ^^

[Matthias_13]

Hallo OwenAuger,

thanks for your example, very easy to follow ^^. I have 1 question: which Visualization did you apply for the Table result  "Top & Other - Rank - ..." i could not find the similar one in my own Power BI. The "Region" and "TopN" are clear with Slicer visualization.

thank you in advance and i am looking forward to your response.

Matthias Bao

[heroncarlos]

Hello Mr. OwenAuger

I'm using your DAX script for "TopN and Others", an excellent script, but now I have a situation where more than one customer has the same score (4) and all of them with ranking 1 (which is right) but the customers that are in the second score (2) has a ranking of 4 (due to the number of customers with ranking 1), is there any way to present the ranking according to the score ?, in this case, customers with ranking 4 would become ranking 2 .

thanks a lot

[heroncarlos]

Hello

I found , just use the parameter <ties> with value = Dense

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Rank =
IF (
AND (
NOT ( ISBLANK ( [Pontuation]) );
ISFILTERED ( Table[name] )
);
RANKX ( ALL (Table[name] ); [Pontuation Amount]; ; ;Dense )
)

[heroncarlos]

Hello  OwenAuger

I've been using your dax script to show Top n-Others for a long time and I think it 's great, but now I have to present a Pie chart or Bar chart with Top n elements and Others too. How can I do this?

thanks a lot

regards

Heron Carlos

[OwenAuger]

Hi @heroncarlos

I was just looking back at this thread and notice I missed your message when I was on vacation!

Your case is slightly different as you need the actual values + "Others" to be in the same dimension, so you need to create an extra table with this dimension.

I have seen a few blog posts on this topic - here is one:

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

[Hspuybroek]

Hi
&nbsp;
Is it possible to do this without the Except function? I am using a Tabular model and I can not choose the Except function. Only the AllExcept function. Your top 10 measure is not problem, but the Other category doens't work.
&nbsp;

To be clear I want it to work dynamic like your example https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=1

Hoe can I use the allexcept function for this? Can’t get it to work.
I did this for example :
&nbsp;
Uitval Top2 = IF([Customer Rank By Selections]&lt;=[Selected Top NNumber];[Selected Top N Value];
IF(HASONEVALUE('ANG Fouten'[LPP_NAME]);
IF(VALUES('ANG Fouten'[LPP_NAME]) = "Overige";
SUMX(FILTER (ALL('ANG Fouten'[LPP_NAME]);[Customer Rank By Selections] &gt;[Selected Top NNumber]);[Selected Top N Value])
)
)
)
&nbsp;
The problem with this that the summation of the tables doesn't include the Other Category. Do you know what a soluction could be?
&nbsp;
Thank you!
&nbsp;
Regards, Hilbert

[OwenAuger]

@Hspuybroek

You can write the "Sales Amount Other" measure in my model without using EXCEPT, but ALLEXCEPT does something different so isn't appropriate here.

I'm not sure whether your version of Tabular allows variables or not, so here are two versions of the "Sales Amount Other" measure with and without variables:

Sales Amount Other NO EXCEPT WITH VARIABLES = 
VAR TopCustomers =
    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] )
RETURN
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS (
            FILTER (
                ALL ( Sales[Customer] ),
                NOT ( CONTAINS ( TopCustomers, Sales[Customer], Sales[Customer] ) )
            )
        )
    )

Sales Amount Other NO EXCEPT WITHOUT VARIABLES = 
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( Sales[Customer] ),
            NOT (
                CONTAINS (
                    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ),
                    Sales[Customer], Sales[Customer]
                )
            )
        )
    )
)

Regards,

Owen

[Hspuybroek]

@OwenAuger Thanks for your help. But the following is empty now:

Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( Sales[Customer] ) ); [Sales Amount Other] )

I saw in your example that it has totals and every else is empty, but in my example everything is empty even the totals. I used your example: Sales Amount Other NO EXCEPT WITHOUT VARIABLES.

The formula works:

Uitval Top Overig = CALCULATE([Selected Top N Value];

KEEPFILTERS(
( FILTER (
ALL('ANG Fouten'[LPP_NAME]);
NOT (CONTAINS(
TOPN([Selected Top NNumber]; ALL('ANG Fouten'[LPP_NAME]);[Selected Top N Value]);'ANG Fouten'[LPP_NAME];'ANG Fouten'[LPP_NAME])
)
))))

But not the formula to get to total only.

Could you help me?

Regards, Hilbert

[Eric_Zhang]

@Hspuybroek

As this thread is already closed, for better response, please post a new thread for your question. Thanks for your understanding. 🙂

[prashappy]

Thanks for awesome Post for finding out TOP 10. I have a question on top of what you have described.

If we have a Quarter column in the table and we need to display value in matrix with Quarter at column level such that whatever will be top 10 based on current quarter. Then, Same top 10 customer should be dispalyed for each quarter. Also, region filter sould apply as it is being applied in your example.

Thanks in advance.

[matts2k7]

This is excellent!

I have managed to integrate into a top 20 / other report but have one question.

How would i adapt so the ranking shows only for the Total row?

I have columns in the report that dont need ranking and this ranks each column seperately 

[digant]

Hi @OwenAuger Thank you for sharing that template. I managed to implement TOP BOTTOM functionality from that solution.

I didnot understand how you managed to sort Matrix. I have created Top & Other table and 2 columns are Index and Top & Other. 

Data type of Index is Whole number, still my Matrix order doesn't work correctly. Appreciate if you can throw some light.

Matrix Order doesn't work

[OwenAuger]

@digant

I'm not sure exactly what's going wrong in your case.

I had a play around with the matrix in my pbix file, and I seem to be able to independently sort "Top & Other" and "Rank" columns within the matrix, just by clicking the column headers. If your Top/Other order is wrong, you should be able to correct it just by clicking the column heading. Does that work?

[anmsharma]

Hi @OwenAuger,

Thanks for providing the exact solution to the problem of many.

I am new to Power BI, implemented your solution and got the desired results except 1 thing.

The Rank 1 is showing as the sum of the entire column for total, attaching the screenshot. Could you please let me know what's the mistake I am making.

Many Thanks,

Anmol

[OwenAuger]

@anmsharma

Hard to say - can you provide more details of your data model- ideally upload a sanitised pbix?

[Jani34]

Hi Owen

And thanks for the great example. I was able to use it when having data without any period. How would you modify it in case i wanted to have periods in columns? Now when adding periods, i'll get top10 for each period, not just top10 for the whole year for example.

BR,

Jani