Forum Discussion
Confusion with Linear Regression (DAX)
Hi there,
I have a railway dataset for prices per day.
I'm having some trouble with understanding simple linear regression using DAX. I understand the formula being used and I've tried to implement it:
Here's what I get when I plot the Price vs. Date as well as the Regression Line. As you can see, it is not a straight line as simple linear regression should be.
Another issue is that it takes the sum of prices per day instead of the average, which is what I want to predict the average prices per day.
How can I fix this issue?
I've attached the pbix file below:
You can do this manually with those formulas, but I'd recommend LINESTX instead.
Either way, you need to aggregate by date before doing your regression.
VAR _Data_ = SUMMARIZE ( ALLSELECTED ( railway ), railway[Date of Purchase], "@SumPrice", SUM ( railway[Price] ) ) VAR _Regression_ = LINESTX ( _Data_, [@SumPrice], railway[Date of Purchase] ) VAR _Slope = SELECTCOLUMNS ( _Regression_, [Slope1] ) VAR _Intercept = SELECTCOLUMNS ( _Regression_, [Intercept] ) VAR _Date = MAX ( railway[Date of Purchase] ) VAR _Result = IF ( NOT ISEMPTY ( railway ), _Date * _Slope + _Intercept ) RETURN _Result
1 Reply
You can do this manually with those formulas, but I'd recommend LINESTX instead.
Either way, you need to aggregate by date before doing your regression.
VAR _Data_ = SUMMARIZE ( ALLSELECTED ( railway ), railway[Date of Purchase], "@SumPrice", SUM ( railway[Price] ) ) VAR _Regression_ = LINESTX ( _Data_, [@SumPrice], railway[Date of Purchase] ) VAR _Slope = SELECTCOLUMNS ( _Regression_, [Slope1] ) VAR _Intercept = SELECTCOLUMNS ( _Regression_, [Intercept] ) VAR _Date = MAX ( railway[Date of Purchase] ) VAR _Result = IF ( NOT ISEMPTY ( railway ), _Date * _Slope + _Intercept ) RETURN _Result