Correlation Coefficient different between excel and Power bi

[Nyx]

Can someone tell me what I have done differently please?

I have the quick measure in power bi, correlation coefficient, category column A, measure x column B and measure y column C. This gave me 0.9882 (to 4 decimals)

In excel I have simple used =Correl() function for A & B columns and have got 0.968161112

date	A	B
01/01/2017	0	0
01/02/2017	200	5
01/03/2017	300	10
01/04/2017	400	16
01/05/2017	300	11
01/06/2017	200	4
01/07/2017	500	26
01/08/2017	600	29
01/09/2017	200	4
01/10/2017	100	1
01/11/2017	300	9
01/12/2017	200	4

[serpiva64]

Hi,

you only need to increase the number of decimals

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Nyx]

Thank you for taking a look for me. I must be doing something wrong in Power bi, i've increased the decimals and got 0.988171688836408

Have I done something wrong here?

A and B correlation for date =

VAR __CORRELATION_TABLE = VALUES('Table'[date])

VAR __COUNT =

    COUNTX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('Table'[A]) * SUM('Table'[B]))

    )

VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[A])))

VAR __SUM_Y = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[B])))

VAR __SUM_XY =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('Table'[A]) * SUM('Table'[B]) * 1.)

    )

VAR __SUM_X2 =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('Table'[A]) ^ 2)

    )

VAR __SUM_Y2 =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('Table'[B]) ^ 2)

    )

RETURN

    DIVIDE(

        __COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,

        SQRT(

            (__COUNT * __SUM_X2 - __SUM_X ^ 2)

                * (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)

        )

    )

[serpiva64 · Accepted solution]

Please, 

i think the problem is in your data

control them 

you can also try again with the data you posted here and you will get the right result

[Nyx]

I did, I found it, thank you. I had a different value in the dummy figures.