ZSequence1ToN = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1)),ASC) Var a = M31*sumOfYs+M32*sumOfXY+M33*sumOfX2Y Var b = M21*sumOfYs+M22*sumOfXY+M23*sumOfX2Y
Var c = M11*sumOfYs+M12*sumOfXY+M13*sumOfX2Y Var M33 = ((countItems*sumOfX2)-(sumOfXs*sumOfXs))/determinant Var M32 = -((countItems*sumOfX3)-(sumOfX2*sumOfXs))/determinant Var M31 = ((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2))/determinant
Var M23 = -((countItems*sumOfX3)-(sumOfXs*sumOfX2))/determinant Var M22 = ((countItems*sumOfX4)-(sumOfX2*sumOfX2))/determinant Var M21 = -((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))/determinant Var M13 = -((sumOfX2*sumOfX2)-(sumOfXs*sumOfX3))/determinant //there was an error on this line i had to add a negative to fix. Var M12 = -((sumOfXs*sumOfX4)-(sumOfX3*sumOfX2))/determinant Var M11 = ((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))/determinant Calculate the inverse matrix (combining a few steps together) Var determinant = countItems*((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))-sumOfXs*((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))+sumOfX2*((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2)) Var countItems = COUNTX(VALUES(Table1),Table1) To get the same polynomial fit as excel, the x values in the calculation are masked over with 1 thru n. Because our Xs (dates) and Ys (measures) are in different tables, we have to temporarily create a new table (Known) with both of those values so we can perform SUMX operations easily. Starting from this solution, I ended up with the following measure:
#Second order regression excel how to
It appears that I can calculate the coefficients for the regression equation correctly, but I just cannot figure out how to correctly return the correct result of the equation to use in a table/chart. I am attempting to implement a 2nd order polynomial regression to fit some data.