Hi,
I've used the dax code to do liner regression in DAX outlined here
https://xxlbi.com/blog/simple-linear-regression-in-dax/
Simple linear regression =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( Table[Column] ),
"Known[X]", [Measure X],
"Known[Y]", [Measure Y]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
RETURN
Intercept + Slope * [Measure X]
However in the EXCEL fucntion LINEST, you can add some options to get back x, x^2 and x^3. What i'm trying to do is get this values back Linest x, linest x^2 and linest x^3. I can get the known x and y, its the bit in the linest function that does the ^{1,2,3}. Linest x is the same as the slope as i think what happens is
y = mx + mx^2 + mx^3 + b
m is the slope, b is the intercept or 1
| Y | X | | Result | Value | EXCEL Formula |
| 91 | 83 | | Linest | -0.237507232 | LINEST(A2:A15,B2:B15) |
| 68 | 43 | | Slope | -0.237507232 | SLOPE(A2:A15,B2:B15) |
| 19 | 98 | | Intercept | 69.48525377 | INTERCEPT(A2:A15,B2:B15) |
| 98 | 53 | | | | |
| 51 | 97 | | Linest x | -0.237507232 | LINEST(A2:A15,B2:B15^{1}) |
| 40 | 86 | | Linest x2 | -0.016413092 | LINEST(A2:A15,B2:B15^{1,2}) |
| 87 | 77 | | Linest x3 | 0.000180521 | LINEST(A2:A15,B2:B15^{1,2,3}) |
| 11 | 74 | | | | |
| 54 | 20 | | | | |
| 37 | 12 | | | | |
| 69 | 28 | | | | |
| 31 | 48 | | | | |
| 91 | 31 | | | | |
| 27 | 87 | | | | |
I've tried updating the DAX to do the y = mx + mx^2 + mx^3 + b but not getting the same results, any help or suggestions???
