Dax Sum of values from many columns in a row

0 votes

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?

Apr 3, 2019 in Power BI by Shubham
• 13,490 points
9,690 views

1 answer to this question.

0 votes

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":

    Unpivot Other Columns

    You should now see this:

    unpivoted view

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

    group by

  5. You should now have the wanted end result:

    summarized view

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"
answered Apr 3, 2019 by Upasana
• 8,620 points

Related Questions In Power BI

0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14, 2019 in Power BI by Cherukuri
• 33,030 points
2,138 views
0 votes
1 answer

In DAX, how do I RETURN the sum of a calculated column from a DAX Table Variable (created via ADDCOLUMN)?

You can access column variables of previously ...READ MORE

answered Oct 8, 2020 in Power BI by Gitika
• 65,910 points
4,084 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,490 points
3,226 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 33,030 points
7,582 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,220 points

edited Oct 11, 2018 by Kalgi 2,097 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,360 points
930 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 23, 2019 in Power BI by VNK
18,425 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,360 points
36,935 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19, 2019 in Power BI by Upasana
• 8,620 points
5,634 views
0 votes
1 answer

Row Level Security not applied through LOOKUPVALUE in Default filter

I see what you mean now. LOOKUPVALUE appears to ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
4,879 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP