Forum Discussion
Least squares approximation
have a poll, and I want to calculate "betas" with the last square aproxximation formula.
I have "Y" (satisfaction), and some other variables as "X"
I have a column with all values of "Y", and one column with all answers of the "X" other answers of the questios of the poll:
Table Name = Registers
Y X QuestionCode
3 3 x1
2 3 x2
4 2 x1
3 x3
4 x2
"X" and QuestionCode columns are bigger than "Y"column.
I want to acces with a filter on QuestionCode, and multiply Y * (all) x1
That is because of the part of the formula that is SUMMATORY ( X*Y ).
That is differrent of SUMMATORY (X) * SUMMATORY (Y)
What I have been trying is SUMX(Registers[Y] * CALCULATE(Registers, Registers[X]) and filtering on the inform sheet with the QuestionCodes, but when I filter it, it turns "zero"
1 Reply
- Greg_DecklerCommunity Champion
If I understand Least Squares Approximation correctly from here:
http://math4all.in/public_html/linear%20algebra/chapter8.5.html
then you will need to do matrix multiplication. I created a Quick Measure for this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/MMULT/m-p/630231
This uses a technique for Transposing tables in DAX that is documented here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/TRANSPOSE/m-p/630229
Or you could use M to do that job if it does not need to be 100% dynamic.