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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dsandberg
Helper II
Helper II

Subtotaling with SUMX and Summarize

I am attempting to subtotal a column using an approached defined in this post. But, despite my best efforts, my subtotal calculation is not returning what I expect. In my report, I am using the matrix visual and I'm stacking a field number (Fld_Number) and then the report number USDA[Report.Number].

 

Report.JPG

 

I expected my [Weighted 6oz] column to report the same row level values as the [raw x factor] column.

 

Weighted 6oz = 
SUMX (
    SUMMARIZE (
        USDA,
        USDA[Report.Number], 
        "RESULTS", [raw_6oz_pct] * [Factor]
    ),
    [RESULTS]
)

I had intended the DAX formula below to show a subtotal of .664 (66.4%). However, I'm not seeing the row level data showing correctly, nor am I getting the subtotal to act correctly.And... the more puzzling thing is that I did have it working correctly, but something changed and I cannot figure out how to re-code my statement to make it work!

 

Can anyone offer any ideas on how to achieve what I'm after?

1 ACCEPTED SOLUTION

if [6oz_pct x factor] is a measure, then should omit the table name from the front of the measure.  This is the only way you can differentiate a measure from a column when reading dax.  read my best practices here. https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/

 

Try this

 

 

_SUMX = SUMX(values(USDA[Report.Number]),[6oz_pct x factor]))

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

10 REPLIES 10

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors