How to not summarize measures

[Minseven]

Hi all,

I have 2 tables: MATERIAL and SALES_MVT.

I want to have a table that list ALL the material without filtering my table due to my summarized measure.

For better understanding of my issue, please find the concerned Pbix:

https://we.tl/t-pJ4Fyh0PcC

Thx for your help

[v-frfei-msft · Accepted solution]

Hi @Minseven ,

To update your measure as below.

QTY_Sold2 = 
VAR _sum =
    CALCULATE (
        SUM ( 'SALES_MVT'[QTY] ),
        FILTER ( SALES_MVT, SALES_MVT[ZONE] = "US" )
    )
RETURN
    IF ( ISBLANK ( _sum ), 0, _sum )

Or we can anable the option show items with no data.

Regards,

Frank

[Minseven]

option SHOW ITEM WITH NO DATA helped me! I completely forgot this option

Thank you

[HotChilli]

The question is unusual because the measure will always filter and summarise, and in this case, it's going to filter for only 'US' material and it's going to SUM the quantity.

If you want to list all the material, you can either add the date to the visualisation (forcing the rows to be unique and the measure will SUM a total for each row)

OR

don't use the measure. Add the QTY field to the visualisation instead and set it to 'Do not summarize'.  You'll still have to filter the table for Zone = 'US' either with a visual filter or write some dax to filter the table.

[Minseven]

Hi,

thank you for your reply. Unfortunatelly, this doesn't solve my problem. The idea was to have the SUM Qty like:

the main idea was to have each column with a certain applied SUM: Columns 2 (only US QTY) Column 3 (CA QTY); ... AND keeping ALL the material list

[HotChilli]

I'm not sure i really understand what you want but can i suggest you experiment with the matrix visualisation (with material in rows and any other quantity measures in the columns