SUMX Multiple Columns Then Store Output in Another Table

[Yoshimitsu411]

Hello All

Thank you in advance for any assistance.

I would like to SUMX the values stored in multiple columns and store the output in a separate table to be used in a bar chart.

Main table:

I want to output the total of each column (except Item column) to a table like this:

So for Jan the value should be 15, Feb will be 5, Mar will be 2 and so on. I'm not sure if I can use Pivot or Unpivot as there are other columns in the main table (not shown in the screenshot).

Result to look similar to this.

Alternatively if there is a way to do this without creating a separate table please let me know.

Thank you.

Yoshi

[dax · Accepted solution]

Hi Yoshimitsu41,

You could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYmMgNgdiIzA/VidaKQkqAxIxg7NBMslAlimSqAlYf2wsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, #"Jan 2019" = _t, #"Feb 2019" = _t, #"Mar 2019" = _t, #"Apr 2019" = _t, #"May 2019" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"Jan 2019", Int64.Type}, {"Feb 2019", Int64.Type}, {"Mar 2019", Int64.Type}, {"Apr 2019", Int64.Type}, {"May 2019", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"sum", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.