Subtotaling with SUMX and Summarize

[dsandberg]

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].

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?

[v-chuncz-msft]

@dsandberg,

It seems that you just need to use DAX below.

Weighted 6oz =
[raw_6oz_pct] * [Factor]

[dsandberg]

The [raw x factor] column DAX code is:

raw x factor = [Factor] * [raw_6oz_pct]

Still looking for a solution that would reflect a subtotal of .664 rather than .625.

[kcantor]

@dsandberg

Here is another resource for you. I am out of the office today and can't find time to log in to see the large screen but this may help:

https://exceleratorbi.com.au/use-sum-vs-sumx/

If that doesn't, maybe @MattAllington will pop by and see this. Or @KHorseman, he is a DAX superstar.

[KHorseman]

I'm trying to replicate the results you're getting and I can't do it just by copying what I see in the screenshot. I can't even get the same subtotals on any column. There must be hidden decimal places for one thing. Also you're summarizing by a column I'm not sure I can even see here. USDA[Report.Number] doesn't seem to be present unless it's the second column on the left. Are there only single values in this table for USDA[Report.Number], or do some Report.Number items appear more than once in the table? That's the only way I can think of to get that 0.625996 result at all.

No way to troubleshoot your results if I can't see how you are getting them in the first place. That SUMX should give the results you expect unless there's some other factor at work that I can't see in the screenshot.

Hi @kcantor. 🙂

[dsandberg]

My apologies; I haven't been communicating this challenge well. Please let me try again. I attempted to implement a DAX expression that "weights" the 6oz_pct relative to the proportion of the report number's [First_Net] vs. the sum of [First_Net] for the [Related.FieldID] in the specific [Crop.Year].

I had expected the subtotal value in the last column at the right to reflect .514 (51.4%) and .664 (66.4%), respectively, at the [Report.Number] level. Or another way to look at it, if you manually subtotal the result of [6oz_pct] * [factor], then you should see .514 (5.14%) and .664 (66.4%) for the fieldID levels.

Also, I have [Farm], [Contract.Number], and [Crop.Year] as slicers.

Here's a better snapshot of the data:

6oz_pct = DIVIDE (
        CALCULATE (
            SUM ( USDA[Total.6oz.Pounds] ),
            FILTER ( USDA, USDA[Related.FieldID] ),
            FILTER ( USDA, USDA[Crop.Year] ),
            FILTER ( USDA, USDA[Report.Number] )
        ),
        CALCULATE (
            SUM ( USDA[USDA.Inspection.Graded.Weight] ),
            FILTER ( USDA, USDA[Related.FieldID] ),
            FILTER ( USDA, USDA[Crop.Year] ),
            FILTER ( USDA, USDA[Report.Number] )
        )
    )

Factor = 
DIVIDE (
    CALCULATE (
        SUM ( USDA[First_Net] ),
        FILTER (
            ALL ( USDA[Related.FieldID] ),
            USDA[Related.FieldID] = USDA[Related.FieldID]
        ),
        FILTER ( ALL ( USDA[Crop.Year] ), USDA[Crop.Year] = USDA[Crop.Year] )
    ),
    CALCULATE (
        SUM ( USDA[First_Net] ),
        FILTER (
            ALL ( USDA[Related.FieldID] ),
            USDA[Related.FieldID] = USDA[Related.FieldID]
        ),
        FILTER ( ALL ( USDA[Crop.Year] ), USDA[Crop.Year] = USDA[Crop.Year] ),
        FILTER (
            ALL ( USDA[Report.Number] ),
            USDA[Report.Number] = USDA[Report.Number]
        )
    )
)

6oz_pct x factor = [Factor] * [6oz_pct]

Should be .514 and .664 = 
SUMX (
    SUMMARIZE (
        USDA,
        USDA[Report.Number], USDA[Crop.Year], USDA[Related.FieldID], 
        "RESULTS", [6oz_pct] * [Factor]
    ),
    [RESULTS]
)

 Hopefully, this is more clear. Thanks for your patience.

[KHorseman]

SUMX takes the sum of the results on each row. The rows for "Should be .514 and .664" are

0.73

0.73

0.63

0.63

0.63

0.60

0.48

If you take the sum of those you get 5.06.

[dsandberg]

Ok, then I have misunderstood and mis-applied the lesson quoted earlier in this thread. I am still unclear if there is a way achieve what I am attempting to do.

Consider the [6oz_pct x factor] column:

1.  row-level data valuea are as expected.

2. Sum of (.248 + .018+ .286 + .080 + .033 ) is .665, but I'm getting a subtotal of .628

Is there a way I can achieve this calculation with DAX?

.

[KHorseman]

6oz_pct x factor = [Factor] * [6oz_pct]

Subtotal and total lines are not sums of the lines above. They are the measure applied at that level of filtering from the rows given on the left of the matrix. The subtotal line for [6oz_pct] is 0.62786 and for [Factor] is 1.0000. Rounded to 3 decimal places, 0.62786 * 1.0000 is indeed 0.628, so the results are correct. If you want the sum of .248 + .018+ .286 + .080 + .033 you would need to write a SUMX formula.

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

[dsandberg]

[6oz_pct x factor] is a measure; the values of .248,  .018, .286,  .080,  .033 are calculated via a measure.

I attempted to follow the suggestion in the link you provided. I tried several iterations of SUMX(VALUE(column name, but since I desire to subtotal a measure, I ended up with :

_SUMX? = 
IF (COUNTROWS ( VALUES ( USDA[Report.Number] ) ) = 1,
    [6oz_pct] * [Factor],
SUMX(USDA,USDA[6oz_pct x factor]))

I admit, I am completely turned-around-and-lost-in-the-woods at the moment. I'm unable to see how this works. If anyone is agreeable to a skype session to help me sort this out (if possible)... i'd be happy to send an invite.

[MattAllington · 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]))