Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sum based on another value

Hi Team,   I have below screenshot for my data Table and Final output expected table.       Thanks, KV's
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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))