Hey all,
I'm looking to create a correlation coefficient measure. I have tried a few different ones that I have found here and elsewhere and they don't seem to be working correctly.
I have health care referral and provider visit data in two different tables connected by a key provider table.
So: table 'Referrals'- referral data
table 'Visit Data' - visit data from our marketing team (visits to the provider)
table 'Provider/NPI Key' - provider key table linking referrals from the provider who was visited
I want to assess the correlation between referrals and visits.
The reason I believe this is off is because when I filter for type of visit, the trend line on my scatter plot turns negative yet the correlation is still listed as 0.7 or more.
Here is the DAX I currently have:
Pearson Correlation Coefficient =
VAR Correlation_Table =
FILTER( ADDCOLUMNS(
VALUES('Provider/NPI Key'[Referred_From_Provider]),
"Value_X", 'Referrals'[Referrals],
"Value_Y", 'Visit Data'[Visits]
),
AND (
NOT (ISBLANK('Referrals'[Referrals])),
NOT (ISBLANK('Visit Data'[Visits]))
)
)
VAR Count_Items =
COUNTROWS(Correlation_Table)
VAR Sum_X =
SUMX (Correlation_Table, [Value_X])
VAR Sum_X2 =
SUMX(Correlation_Table, [Value_X] ^ 2)
VAR Sum_Y =
SUMX(Correlation_Table, [Value_Y])
VAR Sum_Y2 =
SUMX(Correlation_Table, [Value_Y] ^2)
VAR Sum_XY =
SUMX(Correlation_Table, [Value_X] * [Value_Y])
VAR Pearson_Numerator =
Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X =
Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y =
Count_Items * Sum_Y2 - Sum_Y ^2
VAR Pearson_Denominator = SQRT( Pearson_Denominator_X * Pearson_Denominator_Y)
RETURN
DIVIDE (Pearson_Numerator, Pearson_Denominator)
