The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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???
Hi @Anonymous
It seems you may check the variable Known for your scenario. Below are some posts for your reference.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/td-p/247439
https://community.powerbi.com/t5/Desktop/Deming-Linear-Regression-with-DAX/td-p/9550
https://community.powerbi.com/t5/Desktop/DAX-to-create-a-Trend-line/td-p/398438
Regards,
Cherie