Hi,
I'm working with survey data, and a "best practice" is to pivot the dataset, so we end up with one question column and a corresponding value column for that question. As an example, by doing it this way we can easily do an average of the value column, filter for a specific question, and then get the desired output. However, I am trying to create a correlation measure based on this data structure, and I am not getting the correct output. I am not sure where my error occurs, or if it's even possible to do it this way. Any suggestions are greatly appreciated.
This is my current DAX code:
Correlation =
VAR _muX =
CALCULATE (
AVERAGE ( 'Rawdata pivot'[MetricValue] ),
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "1: Overall, I am satisfied with my job"
)
)
VAR _muY =
CALCULATE (
AVERAGE ( 'Rawdata pivot'[MetricValue] ),
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "2.3: Job requirements: My knowledge and skills are useful in my work"
)
)
VAR _numerator =
SUMX (
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "1: Overall, I am satisfied with my job"
),
'Rawdata pivot'[MetricValue]
)
- _muX
* SUMX (
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "2.3: Job requirements: My knowledge and skills are useful in my work"
),
'Rawdata pivot'[MetricValue]
) - _muY
VAR _denominator =
SQRT (
SUMX (
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "1: Overall, I am satisfied with my job"
),
( 'Rawdata pivot'[MetricValue] - _muX ) ^ 2
)
* SUMX (
FILTER (
'Rawdata pivot',
'Rawdata pivot'[Question] = "2.3: Job requirements: My knowledge and skills are useful in my work"
),
( 'Rawdata pivot'[MetricValue] - _muY ) ^ 2
)
)
RETURN
DIVIDE ( _numerator, _denominator )
Best,
MR
