topic Correct Total of Product using Linear Regression % in DAX Commands and Tips

Correct Total of Product using Linear Regression %

codyraptor — Fri, 26 Aug 2022 23:10:57 GMT

I have a measure that calculates the Linear Fcst %. The measure works perfect and I get the correct results by month. I need to take forecast values and multiply against the linear regression %. Simply taking Fcst * Regress % works perfectly fine within a given month, but the total column does not work. I've tried SUMX( values(Date[month]), Fcst*Regress), but the Linear Regression % reverts to looking at only the selected month. I basically lose my forecasted values because the Linear Regression % gets iterated over an individual month. I hope that makes sense. Any help is greatly appreciated...

In the below pic... Booked Sales Linear is correct by month...but the 560,638 is incorrect.

Total Booked Sales Linear has the correct total...but the by month is incorrect.

Below is the blog post providing the fcst logic.

https://xxlbi.com/blog/simple-linear-regression-in-dax/

Re: Correct Total of Product using Linear Regression %

daXtreme — Sun, 28 Aug 2022 07:35:55 GMT

To calculate the correct regression values in the individual cells you have to use ALLSELECTED in its body because ALLSELECTED lets you look at all the selected something you have put on the colums, not only on the individual value in the column.

Re: Correct Total of Product using Linear Regression %

codyraptor — Sun, 28 Aug 2022 14:36:32 GMT

Yep...I'm using allselected in the measure to caluclate the correct regression. It's just when I go to use 'sumx(months, fcst*regression %)'...it seems to remove the 'all selected' within the regression measure.

Re: Correct Total of Product using Linear Regression %

daXtreme — Sun, 28 Aug 2022 15:54:11 GMT

That's most likely because you should never use any measure with ALLSELECTED in it in an iterator. There are many reasons for this limitation and you can find articles about this on www.sqlbi.com. Check this one out: The definitive guide to ALLSELECTED - SQLBI

For the time being I don't have time to look at this in full depth but I'll try to find some time later on. But you should definitely read the article above. ALLSELECTED measures should always be only top-level measures. Sadly, this is not the case in your code and this very likely is the problem.