topic Re: Sum of a calculated column in DAX Commands and Tips

Sum of a calculated column

james_pease — Fri, 02 Sep 2022 21:59:11 GMT

Hello everyone, does anyone know how to calculate the sum of a calculated column? Im trying to create a few measures that use calculated columns for values but they are returning blanks.

Here are my expressions:

measure:

xsqrsum = sum('All_Actual_Sales (2)'[xsq])

Calculated column being referenced:

xsq = if('All_Actual_Sales (2)'[LN Actual Sales]>0,('All_Actual_Sales (2)'[LN Actual Sales])^2)

Calculated column being referenced:

LN Actual Sales = If('All_Actual_Sales (2)'[Actual Sales Null Values]>0,ln('All_Actual_Sales (2)'[Actual Sales Null Values]))

Measure:

xsum = sumx( 'All_Actual_Sales (2)',[LN Actual Sales])

Calculated column being referenced

LN Actual Sales = If('All_Actual_Sales (2)'[Actual Sales Null Values]>0,ln('All_Actual_Sales (2)'[Actual Sales Null Values]))

Measure:

xysum = sum('All_Actual_Sales (2)'[xy])

Calculated column being reference:

xy = if('All_Actual_Sales (2)'[Actual Sales Null Values]>0, sum('All_Actual_Sales (2)'[LN Actual Sales])*sum('All_Actual_Expenses (2)'[Amount]))

Measure:

y-intercept = Divide(

[ysum]*[xsqrsum]-[xsum]*[ysum],

[n]*[xsqrsum]-[xsum]^2)

Calculated coulmns being referenced xsq and xy from above.

So I know my issues stem from not being able to sum the calculated columns.

Thanks in advance!

Re: Sum of a calculated column

daXtreme — Sat, 03 Sep 2022 08:12:39 GMT

OK, you've shown your formulas but you have not shown were the problem really is... Calculated columns are normal columns in a table and they definitely can be used as any other column. Do you get any error? If so, why don't you show it?

Re: Sum of a calculated column

james_pease — Tue, 06 Sep 2022 15:57:31 GMT

The problem is I am expecting to get a number as the total (sum of a calculated column should be a number). I am not getting a number, I am getting a blank. So then when I try and caclulate the sum of xy, the y-intercept, and the slope I received blanks because the sum of x is blank. Lastly, when I try and plug everything into the regression formula, the result is blank because the above are blank.

My new approach is to append two queriers into 1 then use filter or if expressions to sum charts with an expense account number and revenues with revenue account numbers.