cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theo Regular Visitor
Regular Visitor

sum multiple columns, how to?

Hi, Im new in powerBi and Im working in some datat wherein I need to calculate per row with multiple colums.

at 1 row for example, i have data like           1.00     1.25    2.00  3.00   4.50   5.00

now i have to add this, manually i can do it by saying column a + column b.... etc.  but i dont know how to compute via sum

i tried sumx but i have error that says - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

thanks for the help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
magsod Member
Member

Re: sum multiple columns, how to?

Have you tried the proper syntax using SUMX?

MeassureName = SUMX(TableName, TableName[column a] +TableName[column b] +TableName[column c] ...)

 

That should do the trick.

 

Br,

Magnus

5 REPLIES 5
Thiyags Regular Visitor
Regular Visitor

Re: sum multiple columns, how to?

theo Regular Visitor
Regular Visitor

Re: sum multiple columns, how to?

Thank you Thiyags.

I actually have used that manual iteration but am looking for some sum formula.

would like also to do similar calculation like standard deviation by row, etc.

magsod Member
Member

Re: sum multiple columns, how to?

Have you tried the proper syntax using SUMX?

MeassureName = SUMX(TableName, TableName[column a] +TableName[column b] +TableName[column c] ...)

 

That should do the trick.

 

Br,

Magnus

Super User
Super User

Re: sum multiple columns, how to?

In addition to Magnus' correct recommendation, you may also like to consider unpivoting your data into a single column of values.

 

ID, A, B, C, D, E

1, 5, 7, 5, 6, 3

2, 3, 9, 2, 6, 4

 

Is better stored like this

 

ID, Type, Value

1, A, 5

1, B, 7

1, C, 5

1, D, 6

1, E, 4

2, A, 3

2, B, 9

2, C, 2

2, D, 6

2, E, 4

 

This second design will be much more performant and compressed.  You can then also use a simple SUM(Table[Value])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Anonymous
Not applicable

Re: sum multiple columns, how to?

Hi,

The same syntax i used for summing up the data in my table, but it is summing up data of individual column first and then adding to summed up data of another column.

Please Suggest any solution if you have on this. 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,458 guests
Please welcome our newest community members: