Forum Discussion

Yoshimitsu411's avatar
Yoshimitsu411
Resolver I
6 years ago
Solved

SUMX Multiple Columns Then Store Output in Another Table

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

1 Reply

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.