Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Minseven
Helper I
Helper I

How to not summarize measures

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

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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.

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

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.

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

 

Thank you

HotChilli
Super User
Super User

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.

Hi,

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

 

Untitled.pngthe 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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.