Show Total in Matrix

[azaterol]

Hello everyone,

I have a matrix with items.

Sales on January for Cola 16,05 €: 

The sales formula is:

Sales € = CALCULATE(SUMX(AUPOS,AUPOS[Quantity]*AUPOS[AUMAT.VK]))

I would now like to have the total of all sales as a column. How can I get that done?

This I want:

After that I want to calculate the percentage sales of each item to the total sales

I've already tried everything. Filters are active in this matrix, so please do not use any formulas to deactivate the filters.

Many thanks for your help.

[anmolmalviya05 · Accepted solution]

Hi @azaterol, Hope you are doing good!

You can create a new measure to calculate total sales using below formula:

Total Sales = CALCULATE(

    SUM('Demo table'[Sales]),

    REMOVEFILTERS('Demo table')

)

Solution:-

Note: the remove filter will only work for the created measure, it wouldn't affect your visual or any other thing.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[SamWiseOwl · Accepted solution]

Hi @azaterol ,

If you create a new measure then you can remove the filters without affecting the other columns.

We have a free video on how to remove filters in the Wise Owl Dax series.

The calculation would be:
Return sales total=
Calculate(
               [Sales €] -- Your existing measure

               ,ALLSELECTED(Table[ColumnToRemoveFilter]) --This removes internal filter but keeps external

)

If the items in your visual are columns you would need to remove both of their filters:
ALLSELECTED(Table[Artikel-Nr.], Table[Artikel]) --replace table with your actual table name

Finally create a final measure that divides the two:
% Sales = [Sales]/[Return sales total]

If this is what you are looking for please mark as a solution so others can find it 🙂

[anmolmalviya05 · Accepted solution]

Hi @azaterol, Hope you are doing good!

You can create a new measure to calculate total sales using below formula:

Total Sales = CALCULATE(

    SUM('Demo table'[Sales]),

    REMOVEFILTERS('Demo table')

)

Solution:-

Note: the remove filter will only work for the created measure, it wouldn't affect your visual or any other thing.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!