Forum Discussion
Sum based on another value
Hi Team,
I have below screenshot for my data Table and Final output expected table.
Thanks,
KV's
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
2 Replies
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))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
