R-Squared measure in power pivot

Question

I'm trying to add a DAX measure for R^2 in PowerPivot. I saw the answer linked and my current code is as follows:

=CALCULATE(
DIVIDE(
SUMX( 'Table'; ('Table'[Dates] - AVERAGE('Table'[Dates])  * 
 ('Table'[Daily Result ($)]) - AVERAGE('Table'[Daily Result ($)]) ) );
 (SQRT(SUMX( 'Table'; ('Table'[Dates]  - AVERAGE('Table'[Dates])) ^2 ) ) ) *
(SUMX('Tablw'; ('Table'[Daily Result ($)])- AVERAGE('Table'[Daily Result ($)]) ^2)))^2
)

X axis is a sequence of dates and Y axis is a sequence of values. But this gives me nonsense values, sometimes well above one, while Excel's RSQ formula gives proper ones. How do I fix it/What am I doing wrong?

Have you tried the formula I suggested in the answer linked? Yours is similar but tries to substitute the variables inside the CALCULATE and SUMX and also has some misplaced parentheses. — Alexis Olson, Jan 18 '21 at 22:08
I did try that one, it still gives wildly different values than RSQ over the same data. — Eduardo Santos, Jan 18 '21 at 23:08
I’d expect it to be wildly different if tried it as written in your post. Separately, it would help to provide some example data and desired result in your question so that an answer can be verified. — Alexis Olson, Jan 18 '21 at 23:14

score 0 · Answer 1 · answered Jan 19 '21 at 16:57

I give the formula for Correl in the linked post. You can either define this and write a new measure that references it, i.e.,

RSq = [Correl] ^ 2

Or else write a measure for RSq independently where you add a line to do the squaring

RSq =
VAR AvgX = AVERAGE ( Table1[x] )
VAR AvgY = AVERAGE ( Table1[y] )
VAR Correl =
    DIVIDE (
        SUMX ( Table1, ( Table1[x] - AvgX ) * ( Table1[y] - AvgY ) ),
        SQRT (
            SUMX ( Table1, ( Table1[x] - AvgX ) ^ 2 ) *
            SUMX ( Table1, ( Table1[y] - AvgY ) ^ 2 )
        )
    )
RETURN
    Correl * Correl

Or you can square the numerator and denominator for a bit more efficient formula:

RSq = 
VAR AvgX = AVERAGE ( Table1[x] )
VAR AvgY = AVERAGE ( Table1[y] )
RETURN
    DIVIDE (
        SUMX ( Table1, ( Table1[x] - AvgX ) * ( Table1[y] - AvgY ) ) ^ 2,
        SUMX ( Table1, ( Table1[x] - AvgX ) ^ 2 ) *
        SUMX ( Table1, ( Table1[y] - AvgY ) ^ 2 )
    )

R-Squared measure in power pivot

1 Answers1