Quick Measure Correlation Coefficient

[Anonymous]

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?

[richardburling]

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

[Fowmy · Accepted solution]

@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)
		)
	)

[Pragati11]

Hi @Anonymous ,

You can check this explanation here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Correlation-coefficient/td-p/196274

https://xxlbi.com/blog/pearson-correlation-coefficient-in-dax/

Thanks,

Pragati

[Anonymous]

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