Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Quick Measure Correlation Coefficient

I am trying to calculate the correlation coefficient using a quick measure. However I am kind of puzzled by the fields is requires.

 

I come from a R background and here we only send in 2 column to calculate the pearson coefficient. However the quick measure asks me for a third one, the category.

 

What is this category?

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    You can use the Quick Measure after adding an Index column to your dataset if you do not have a column with unique values. This is required by the Quick Measure to calculate the correlation coefficient over a category.

    If you really need to calculate with only two columns then find below the modified measure. I have also attached the PBIX file. Do verify the result from your model. 



     

    Age and Gluco correlation = 
VAR __CORRELATION_TABLE = 'Table'
VAR __COUNT =
	COUNTX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) * SUM('Table'[Gluco]))
	)
VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[Age])))
VAR __SUM_Y = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[Gluco])))
VAR __SUM_XY =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) * SUM('Table'[Gluco]) * 1.)
	)
VAR __SUM_X2 =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) ^ 2)
	)
VAR __SUM_Y2 =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Gluco]) ^ 2)
	)
RETURN
	DIVIDE(
		__COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,
		SQRT(
			(__COUNT * __SUM_X2 - __SUM_X ^ 2)
				* (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)
		)
	)

     

     

     

     

     

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      People asing the same question there do not seem to be getting a concrete answer.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    You can use the Quick Measure after adding an Index column to your dataset if you do not have a column with unique values. This is required by the Quick Measure to calculate the correlation coefficient over a category.

    If you really need to calculate with only two columns then find below the modified measure. I have also attached the PBIX file. Do verify the result from your model. 



     

    Age and Gluco correlation = 
VAR __CORRELATION_TABLE = 'Table'
VAR __COUNT =
	COUNTX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) * SUM('Table'[Gluco]))
	)
VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[Age])))
VAR __SUM_Y = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE(SUM('Table'[Gluco])))
VAR __SUM_XY =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) * SUM('Table'[Gluco]) * 1.)
	)
VAR __SUM_X2 =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Age]) ^ 2)
	)
VAR __SUM_Y2 =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE(SUM('Table'[Gluco]) ^ 2)
	)
RETURN
	DIVIDE(
		__COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,
		SQRT(
			(__COUNT * __SUM_X2 - __SUM_X ^ 2)
				* (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)
		)
	)

     

     

     

     

     

     

  • richardburling's avatar
    richardburling
    Helper I
    4 years ago

    Hi all, I have used the quick measure to calculate the correltion (r value), however, it has returned a value greater then 1. Has anyone come acorss this error in their calculations? Obviously they must be an error as an R vlaue can only be between -1 and 1.

    Thanks

    Rich