SUMX of the MAXX variable

[Anonymous]

Hello everyone,
I don't find the solution. I want to calculate a Regression. I want to know how i can calculate the SUMX of MAXX.

Maybe you find a way?
Best regards

=VAR FilterRow = FILTER('Query_NIKKEI_225';'Query_NIKKEI_225'[timestamp]=EARLIER('Query_NIKKEI_225'[timestamp]) && EARLIER('Query_NIKKEI_225'[Differenz Datum])>=0) VAR FilterRow2 = FILTER('Query_NIKKEI_225';EARLIER('Query_NIKKEI_225'[Differenz Datum])>=0) VAR yStern =MAXX(FilterRow;LN(Query_NIKKEI_225[(high + low )/2])) VAR ySternQuer = AVERAGEX(FilterRow2;LN(Query_NIKKEI_225[(high + low )/2])) VAR ySternMinusySternQuer = MAXX(FilterRow;yStern-ySternQuer) VAR xStern =MAXX(FilterRow;'Query_NIKKEI_225'[Rang Datum]) VAR xSternQuer = AVERAGEX(FilterRow2;'Query_NIKKEI_225'[Rang Datum]) VAR xSternMinusxSternQuer = MAXX(FilterRow;xStern-xSternQuer) VAR xMinusxQuerMalySternMinusySternQuer=MAXX(FilterRow;xSternMinusxSternQuer*ySternMinusySternQuer) Return SUMX(FilterRow2;CALCULATE(MAXX(FilterRow;xSternMinusxSternQuer*ySternMinusySternQuer)))

[technolog · Accepted solution]

To calculate a regression in Power BI using DAX, and specifically to find the SUMX of a MAXX variable, we need to approach the calculation step-by-step. The provided DAX formula contains several variables to calculate intermediate values necessary for the regression calculation.

Given the complexity of the DAX formula, let's break it down and rebuild it to ensure that the SUMX of MAXX is correctly calculated. The following steps illustrate how to achieve this:

Calculate yStern (y):

yStern =
VAR FilterRow = FILTER('Query_NIKKEI_225', 'Query_NIKKEI_225'[timestamp] = EARLIER('Query_NIKKEI_225'[timestamp]) && EARLIER('Query_NIKKEI_225'[Differenz Datum]) >= 0)
RETURN MAXX(FilterRow, LN('Query_NIKKEI_225'[(high + low) / 2]))

Calculate ySternQuer (Average y):

ySternQuer =
VAR FilterRow2 = FILTER('Query_NIKKEI_225', EARLIER('Query_NIKKEI_225'[Differenz Datum]) >= 0)
RETURN AVERAGEX(FilterRow2, LN('Query_NIKKEI_225'[(high + low) / 2]))

Calculate ySternMinusySternQuer (y - Average y):

ySternMinusySternQuer =
VAR yStern = MAXX(FilterRow, LN('Query_NIKKEI_225'[(high + low) / 2]))
VAR ySternQuer = AVERAGEX(FilterRow2, LN('Query_NIKKEI_225'[(high + low) / 2]))
RETURN yStern - ySternQuer

Calculate xStern (x):

xStern =
VAR FilterRow = FILTER('Query_NIKKEI_225', 'Query_NIKKEI_225'[timestamp] = EARLIER('Query_NIKKEI_225'[timestamp]) && EARLIER('Query_NIKKEI_225'[Differenz Datum]) >= 0)
RETURN MAXX(FilterRow, 'Query_NIKKEI_225'[Rang Datum])
Calculate xSternQuer (Average x):*

DAX
Копировать код
xSternQuer =
VAR FilterRow2 = FILTER('Query_NIKKEI_225', EARLIER('Query_NIKKEI_225'[Differenz Datum]) >= 0)
RETURN AVERAGEX(FilterRow2, 'Query_NIKKEI_225'[Rang Datum])

Calculate xSternMinusxSternQuer (x - Average x):
xSternMinusxSternQuer =
VAR xStern = MAXX(FilterRow, 'Query_NIKKEI_225'[Rang Datum])
VAR xSternQuer = AVERAGEX(FilterRow2, 'Query_NIKKEI_225'[Rang Datum])
RETURN xStern - xSternQuer

Calculate xMinusxQuerMalySternMinusySternQuer:

xMinusxQuerMalySternMinusySternQuer =
VAR xSternMinusxSternQuer = MAXX(FilterRow, xStern - xSternQuer)
VAR ySternMinusySternQuer = MAXX(FilterRow, yStern - ySternQuer)
RETURN xSternMinusxSternQuer * ySternMinusySternQuer

Return the final SUMX of MAXX variable:

RegressionSumX =
VAR FilterRow2 = FILTER('Query_NIKKEI_225', EARLIER('Query_NIKKEI_225'[Differenz Datum]) >= 0)
VAR xMinusxQuerMalySternMinusySternQuer =
MAXX(
FilterRow,
VAR xSternMinusxSternQuer = MAXX(FilterRow, xStern - xSternQuer)
VAR ySternMinusySternQuer = MAXX(FilterRow, yStern - ySternQuer)
RETURN xSternMinusxSternQuer * ySternMinusySternQuer
)
RETURN SUMX(FilterRow2, xMinusxQuerMalySternMinusySternQuer)