Sum based on another value

[Anonymous]

Hi Team,

I have below screenshot for my data Table and Final output expected table.

Thanks,

KV's

[Anonymous · Accepted solution]

HI @Anonymous,

I'd like to suggest you add a variable to summary these records and use the iterator function(sumx) on this variable to apply second aggregations.

Measure =
VAR summary =
    SUMMARIZE (
        Table,
        [Month],
        "Diff",
            LOOKUPVALUE ( RawTable[Target], RawTable[VALUES], [Month] )
                - LOOKUPVALUE ( RawTable[Target], RawTable[VALUES], "Total " & [Month] )
    )
RETURN
    SUMX ( summary, [Diff] )

The 'table' is a new calculate table with all month names, they are used as the category of table visual.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Create a new column and use in visual with new measure suggested below

new column = SUBSTITUTE([Values] ,"Total ","")

measure =
var _1 = calculate(sum(Table[Target]) , filter(Table, search("Total",Table[value],,0)= 0)) - calculate(sum(Table[Target]) , filter(Table, search("Total",Table[value],,0)>0))
Var _2 = calculate(sum(Table[Target]) , filter(Table, search("Total",Table[value],,0)>0))
return
sumx(Values(Table[New column]), if(_2=0, 0, _1))