cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raymond Member
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?

 

2018-02-11 18_50_35-Mappe1 - Excel.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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

 

sumvalues.png

Community Support Team
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]

1.PNG

 

 

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]

 2.PNG

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])

 3.PNG

 

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

 

Regards,

Jimmy Tao

raymond Member
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
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

deeksha_12 Frequent Visitor
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
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.