Forum Discussion

Nyx's avatar
Nyx
Frequent Visitor
3 years ago
Solved

Correlation Coefficient different between excel and Power bi

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

 

dateAB
01/01/201700
01/02/20172005
01/03/201730010
01/04/201740016
01/05/201730011
01/06/20172004
01/07/201750026
01/08/201760029
01/09/20172004
01/10/20171001
01/11/20173009
01/12/20172004
Need Help
  • serpiva64's avatar
    serpiva64
    3 years ago

    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

4 Replies

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      Nyx
      Frequent Visitor
      3 years ago

      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's avatar
        serpiva64
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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