Calculate sum across many columns per row

[Anonymous]

Hello,

I have the following table in Power BI and i am looking for a simple or easy way to sum across columns per row.

For example, i have 3 columns to sum into a fourth column per row. But it becomes very complicated if i have 100's of columns to add. So, i am looking for an efficient and easy formula. I am wondering if this can be done in DAX using a measure or calculated column? Which is the best way? Is there an easy way to omit one or more specific columns from the sum?

Col1	Col2	Col3	Sum
4	5	6	15
7	3	3	13
8	3	3	14
8	2	7	17
4	2	2	8
3	6	9	18
2	3	4	9
10	3	7	20
6	7	8	21
3	2	5	10
3	9	3	15
7	6	3	16

Any help is much appreciated!

[tamerj1 · Accepted solution]

@Anonymous 
The best method is to unpivot the columns using power query https://we.tl/t-4Ssllc7A7P

@Anonymous This is the solution using DAX calculated table

New Data1 = 
UNION (
    SELECTCOLUMNS ( Data1, "Index", [Index], "Column", "Col1", "Value", [Col1] ),
    SELECTCOLUMNS ( Data1, "Index", [Index], "Column", "Col2", "Value", [Col2] ),
    SELECTCOLUMNS ( Data1, "Index", [Index], "Column", "Col3", "Value", [Col3] )
)

[Anonymous]

@tamerj1 Thanks for your DAX solution. I have been doing more research and found maybe a simpler way. I tested the following in a calculated column and it seems to work?! But i am wondering if this could be simplified even more, by referencing the entire table instead of each column, one by one? This would be tedious and long to type for a table with 100 columns.

SumValues= [Col1]+[Col2]+[Col2]

[tamerj1]

@Anonymous 

 Unfortunately summing the columns one by one is the only solution. Having 100columns is extremely unusual and is a clear indication that there is something wrong in the structure of your data. Now you are suffering to have the sum but what about if you need to create a measure? Can you imagine having to duplicate each measure 100 times? Nobody does that. It should be only one attributes column and value column then everything would be just easy. 

[Anonymous]

@tamerj1 Thanks for your reply! I appreciate your solution but i am looking for an answer using DAX - is it possible?

[tamerj1]

@Anonymous 
Yes but it is not as simple as power query. I will update the solution in the first reply in few minutes.