SUMX/Calculate Table on data range

[Jerry-P4P]

Good day community,

I am trying to add some values from different colums into a total value. Sadly I don't seem to get it to work..

In the screenshot you can see what I am trying to achieve. I want to make a new Colum where the Total value is displayed (The red part of the printscreen). I need to sum the values of the data rows together, but I need to not include the Text columns( Columns 10 & 9). Obviously the range goes for the whole year, so I don't want to manually put all the columns in the formula, because that would be kinda a waste of time. (Certainly when it comes to more then 1 year 😅)

I tried it first with Calculate(SUMX(TableName,ALLEXPECT(TableName,Columns), but still then I get the error about multiple columns cannot be converted to a scalar value.

Ps. I did already change the format of the values to decimal numbers. Still I keep getting this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Any help is greatly appreciated.

Cheers,

Jerry

[PaulOlding · Accepted solution]

Hi @Jerry-P4P 

I'd recommend unpivotting the data in Power Query. 

That will get you a table with a single Date column (which you currently have as separate columns) with a corresponding Amount column.

Your measure is then

Total Amount = SUM(Table[Amount])

[Jerry-P4P]

Thanks for your responses!

It seem to be working so far when I use unpivot columns on all the date columns!

[Anonymous]

Hi @Jerry-P4P,

We do not so recommend you to store the date values in columns, a table with too many columns should affect the performance.

Optimization guide for Power BI - Power BI | Microsoft Docs
In addition, power bi is not suitable to directly calculate across multiple columns.

For this scenario, I'd like to suggest you refer to the following link to 'unpivot column' the date fields, then you can simply calculate and summary these field values.

Unpivot columns (Power Query) (microsoft.com)

Regards,

Xiaoxin Sheng

[Whitewater100 · Accepted solution]

Hi What Paul said is good.

One minor add on is you will want to click the top of each text column in PQ then choose for the drop down "Unpivot Other Columns"

OR

Highlight all those data columns only and "Unpivot Columns". Then you just have to sum up one column to get your total as  suggested. 

[PaulOlding · Accepted solution]

Hi @Jerry-P4P 

I'd recommend unpivotting the data in Power Query. 

That will get you a table with a single Date column (which you currently have as separate columns) with a corresponding Amount column.

Your measure is then

Total Amount = SUM(Table[Amount])

[amitchandak]

@Jerry-P4P ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.