Create a column that gives conditionnal sum

[Gavroche97]

Hi,

I would like to create a column that would give the sum of a column for each occurences of another column.

Exemple:

Cat   Value   SumColumn

A       3              4      

A       1              4

B       4              9

B       5              9

C       7             16

C       8             16

C       1             16

That would be simple to reproduce in any programming langage but I can't get the way to do it in M or DAX. Can anyone help me ?

Thank you,

Victor

[m_alireza]

Hi @Gavroche97 ,

You can achieve this by using the group by function in Power Query. 

Copy the below query in your advanced editor and amend as needed: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJWitWBsAzBLCcgywTOMgWznIEsczjLAs4C6ogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cat"}, {{"SumColumn", each List.Sum([Value]), type nullable number}, {"AllRows", each _, type table [Cat=nullable text, Value=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Cat", "Value"}, {"Cat.1", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows",{"Cat.1"})
in
    #"Removed Columns"

Sample output: 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column by writing DAX.

Sum Column =
SUMX (
    FILTER ( Data, Data[Category] = EARLIER ( Data[Category] ) ),
    Data[Value]
)