Forum Discussion

MrMP's avatar
MrMP
Helper III
5 years ago
Solved

Subtotal based on measure

Hi all,

 

I have table:

 

KeyQuantityMeasureMeasure2
AA500
AA12130 (12+8+10)
AA8130 (12+8+10)
AA10130 (12+8+10)
BB313

 

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!

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    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.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
      MrMP
      Helper III
      5 years ago

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

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        5 years ago

        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

         

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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.