Forum Discussion
Quick Measure Correlation Coefficient
- 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) ) )
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)
)
)