Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Solved! Go to Solution.
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
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
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |