topic Re: SUMX/Calculate Table on data range in DAX Commands and Tips

SUMX/Calculate Table on data range

Jerry-P4P — Mon, 10 Jan 2022 15:44:56 GMT

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

Re: SUMX/Calculate Table on data range

amitchandak — Mon, 10 Jan 2022 16:09:46 GMT

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

Re: SUMX/Calculate Table on data range

PaulOlding — Mon, 10 Jan 2022 17:50:43 GMT

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

Re: SUMX/Calculate Table on data range

Whitewater100 — Mon, 10 Jan 2022 19:54:37 GMT

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"

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

Re: SUMX/Calculate Table on data range

Anonymous — Thu, 13 Jan 2022 01:20:40 GMT

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

Re: SUMX/Calculate Table on data range

Jerry-P4P — Fri, 14 Jan 2022 08:17:30 GMT

Thanks for your responses!

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