Sum by TOPN and Keep the total of all clients

[Luis_Caston]

Dear all,

I've the next problem:
I want to filter by TOP 5 clients but I want to keep the total. If I introduce a filter here, the total is only the total of the clients filtered.

Do you know how can I sum by Top 5 clients (making a ranking for example as TOP 5 & Others by sales and make another category as "other" to keep the whole total?

I add a pbix Sum by ranking 

[Luis_Caston]

Dear @timalbers @nsexton12 thank you for your support.

I've tryed both options and I've the next doubts:

@timalbersI'd like to see the whole total (450) but at the same time the % of the total (450) with the TOP 5 clients, not the % of the total filtered that is the point 1 (White 25% is the % of the total of this TOP 5 clients). Do you know how to do it?

@nsexton12I'm thinking if not do it in this way, I'll put the total in millions and total million, but I'd like to do it in % as in the first option. Do you also know how to do it?

[timalbers · Accepted solution]

You may create a measure for this:

Suma de Sales Percentage = 
    DIVIDE( 
        SUM( Tabla[Sales] ),
        CALCULATE( SUM( Tabla[Sales] ), ALL( Tabla ) )
    )

[Luis_Caston]

You got it!!!

Thank you so much that is what I was looking for!!

Great!!

I've updated the pbix if someones needs it.

Thank you again guys! appreciate!

[timalbers]

Additionally, if you wish to have the number in the same table visual, you could add a measure like this:

Suma de Sales = 
    IF( 
        ISINSCOPE( Tabla[Client] ), 
        BLANK(), 
        CALCULATE( 
            SUM( Tabla[Sales] ), ALL( Tabla )
        )
    )

Then you could add this measure as additional column to your table visual.

The grand total will then only show for the total row and be blank for each client.

[nsexton12]

Have you thought about turning the sum column option off for the table and creating a new measure that is the sum of all sales? You could put on a data card that is directly beneath the table to keep the value in the same area. The formula would be generally structured as CALCULATE(SUM(Table[Sales]),ALL(Table)). If this answers your question, please mark as a solution!

[nsexton12]

It would look roughly like this.