cancel
Showing results for
Did you mean:
Member

## Sum values of multiple columns per row

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Sum values of multiple columns per row

@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

6 REPLIES 6
Super User

## Re: Sum values of multiple columns per row

HI @raymond

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

Community Support Team

## Re: Sum values of multiple columns per row

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:

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

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

Member

## Re: Sum values of multiple columns per row

@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.

Super User

## Re: Sum values of multiple columns per row

@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

Frequent Visitor

## Re: Sum values of multiple columns per row

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])`
Super User

## Re: Sum values of multiple columns per row

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.