Sum values of multiple columns per row

[raymond]

Dear Community,

it seems so simple, yet I havent found an adequate solution.

Initial Situation:

There is a table of multiple columns. Some of which are named button_1, button_2 etc. Sometimes there are values in those columns.

Sought Output as a new column:

The function should sum up the values of multiple columns. 

In that example it is a simple sum function from button_1 to button_4.

How can this be achieved?

[v-yuta-msft]

Hi Raymond,

To achieve your requirement, there’re two solutions as follow:

1.You can create a new column [Output], and use the Dax formula below:

Output = Table1[button_1] + Table1[button_2] + Table1[button_3] + Table1[button_4]

2.In edit query, first convert all data in your table to number, then follow steps below:

1. click Add Column
2. click Custom Column
3. input new column name
4. input custom column formula: Output = [button_1] + [button_2] + [button_3] + [button_4]

 

You can also add M code as below in Advanced Editor:

#"Added Custom" = Table.AddColumn(#"Previous Step Name", "Output", each [button_1] + [button_2] + [button_3] + [button_4])

 

Please refer to PBIX file: https://www.dropbox.com/s/2q25lxuug3c9y7c/For%20raymond.pbix?dl=0

Regards,

Jimmy Tao

[deeksha_12]

Hi, 

I have applied the second approach for taking out the sum through Power Query. 

But I am still getting an error.

And when i clicked on the error it shows this : 

Expression.Error: We cannot apply operator + to types Number and Text.
Details:
Operator=+
Left=8
Right=0

There are nine columns which i want to add and every column has numeric value and no blank cell.

= Table.AddColumn(#"Removed Columns1", "Facilitation Skills", each [form.Following_session_flow] + [form.Relevant_and_effective_icebreaker] + [form.Using_concrete_familiar_language_with_relatable_examples] + [form.Voice_Clarity_Volume_Modulation] + [form.Displaying_effective_nonverbal_communication] + [form.Handling_differences_of_opinion_and_conflicts_group_dynamics_during_the_session_with_participant_and_the_environment_surrounding_the_session] + [form.Ask_questions_appropriately_to_allow_participant_to_think_respond_to_participants_questions] + [form.Time_management] + [form.Closing_session_with_a_quick_recap_of_key_learning])

[Ashish_Mathur]

Hi,

Atleast one of the columns is formatted as text/alphanumeric.  Click on any step before the summation step, select all numeric columns > right click > Change Type > Decimal.

Hope this helps.

[raymond]

@v-yuta-msftand  @Zubair_Muhammad thank you so much. This was easy - but isnt there another way to maybe define a range of columns where the values need to be summed up or do I really have to name every single column.

Suppose I have a table with 200 columns and I want to summarize all of them quickly.

[Zubair_Muhammad · Accepted solution]

@raymond

In case you have 200 columns to sum....select the 1st column>>press the shift key>>>scroll to the last (200th column) and select it.

This way all columns will be selected

Then you can add a new column summing all 200 columns......... from the "ADD COLUMN" tab >>>"STATISTICS">>>"SUM"...... as I mentioned above

[Tellpauri2203]

It worked. Thanks. 

[Zubair_Muhammad]

HI @raymond

Go to Query Editor>>> Select the Button Columns>>>Go to "Add Column" Tab>>>Statistics>>>Sum