Subtotal based on measure

[MrMP]

Hi all,

I have table:

Key	Quantity	Measure	Measure2
AA	5	0	0
AA	12	1	30 (12+8+10)
AA	8	1	30 (12+8+10)
AA	10	1	30 (12+8+10)
BB	3	1	3

How is it possible to get subtotal based on Measure, so that in Measure2 I have subtotal of Key AA and not summing where measure is 0.

Thank you all for support so far, this is such a great community!

[v-henryk-mstf · Accepted solution]

Hi @MrMP ,

The method of nesting measures may cause calculation logic errors. I suggest replacing the measure with a calculate column, and then creating a measure to get the sum value corresponding to column 1.

M =
CALCULATE (
    SUM ( 'Table'[Quantity] ),
    FILTER ( ALL ( 'Table' ), 'Table'[calculated column] = 1 )
)

If the problem is still not resolved, please provide detailed error information . Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please try this measure expression.  Replace Qty and Measure1 with your actual table and measure names.

Exclude Zeros =
VAR vSummary =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Qty, Qty[Key], Qty[Quantity] ),
            "cMeasure1", [Measure1]
        ),
        ALLEXCEPT ( Qty, Qty[Key] )
    )
VAR vResult =
    SUMX ( FILTER ( vSummary, [cMeasure1] > 0 ), Qty[Quantity] )
RETURN
    IF ( [Measure1] = 0, 0, vResult )

Pat

[MrMP]

Thank you for the solution. But for some reason I couldnt use it since I ran out of memory.

[mahoneypat]

Please confirm you used it as a measure and not a column.  Also, can you share more about your model (tables, # rows, relationships, etc.) and the expression you tried?

Pat

[MrMP]

Yes I did use it as measure. Well the whole pbix is really complex. Probably has 100+ measures, # of rows is ~40k, 80 pages. There are only few relationships and they are just small master tables.