Dax Sum of values from many columns in a row

Question

So, what i want is to sum all values in each row and display them in a calculated column. This is what I'm doing.

CalculatedColumn = 'public table_name'[column1] + 'public table_name'[column2] + ... + 'public table_name'[column528]

Is there a better way of doing this?

Answer 1

Yes, you can. Try to "Unpivot other columns" and then "Group By" using the Query Editor.

1. Suppose this is your dataset:

   item;col1;col2;col3;col4;col5
   apple;1;2;3;4;5
   orange;1;2;3;5;8
   banana;1;2;4;6;8
   

2. Load it, and open the query editor.

3. Choose "Unpivot Other Columns":

   

   You should now see this:

   

4. On the "Transform" tab in the ribbon, choose the leftmost "Group By" option. And fill out the dialog like so:

   

5. You should now have the wanted end result:

   

PS: this is the Power Query that is generated for you:

let
    Source = Csv.Document(File.Contents("D:\Experiments\PowerBi\denormalized.csv"),[Delimiter=";", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"item", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"item"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"item"}, {{"SumCol", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"