Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to Calculate Subtotal of Other Rows (Exclude Current Row Value)

I am looking for a way to generate a subtotal column that's the sum of other rows of data, so in other words, it ignores / excludes the current row's value from the subtotal.   Per the screenshot ...
Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    Anonymoustry this

     

    _test = 
IF (
    OR (
        OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
        ISINSCOPE ( SM[loc] )
    ),
    CALCULATE ( SUM ( CS1[metric1_subtotal] ), ALLSELECTED ( CS1 ) )
        - SUMX ( CS1, CS1[metric1_subtotal] ),
    IF (
        HASONEVALUE ( SM[sdv] ),
        VAR _1 =
            MAX ( SM[sdv] )
        VAR _2 =
            CALCULATE (
                SUMX ( CS1, CS1[metric1_subtotal] ),
                FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
            )
        RETURN
            _2,
        SUMX ( CS1, CS1[metric1_subtotal] )
    )
)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

smpa01  - Thanks for sharing this approach!

It seems to work fine at the 'sdv' level (Level-1), but when i drill down to 'srg' (Level-2), it doesn't seem to follow the drill-level rows.

 

 

Thoughts?

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous  try this and see if it works as you desired

 

Measure :=
IF (
    OR (
        OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
        ISINSCOPE ( SM[loc] )
    ),
    SUMX ( CS1, CS1[metric1_subtotal] ),
    IF (
        HASONEVALUE ( SM[sdv] ),
        VAR _1 =
            MAX ( SM[sdv] )
        VAR _2 =
            CALCULATE (
                SUMX ( CS1, CS1[metric1_subtotal] ),
                FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
            )
        RETURN
            _2,
        SUMX ( CS1, CS1[metric1_subtotal] )
    )
)