Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |