How to set up correlation measure with row level results

[bdue]

Hi all,

I have searched several threads on correlations and the functionality of the correlation quick measure, but am a fairly basic DAX user, so can't interpret most of the commentary.  I feel like this is a fairly straightforward calculation I am looking for, but the Correlation quick measure is only returning a result in the table total, not for each row as I am trying to find.

The basic use case here is employee survey data and I want to correlate the response of each question in the dataset to the overall "willingness to recommend" question.  

Here is sample of the data, with each survey having a [SurveyID] and then all of the questions on the survey listed, and then the score on each survey as well as a new column I added to parse out the "willingness to recommend" value so I could hopefully correlate the "average of answer numeric" values for each question to the "willingness to recommend" question across the dataset. 

The second table below shows the result of what I get from the correlation quick measure, with no values at the question level as I am hoping to get, only a .56 correlation value in the table total.  Dax from the quick measure output is pasted below.

I appreciate anyone able to take a look!

VoECorrelation =

VAR __CORRELATION_TABLE = VALUES('STEmployeeData'[SurveyID])

VAR __COUNT =

    COUNTX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(

            SUM('STEmployeeData'[WillingnessScore])

                * SUM('STEmployeeData'[OtherScore])

        )

    )

VAR __SUM_X =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('STEmployeeData'[WillingnessScore]))

    )

VAR __SUM_Y =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('STEmployeeData'[OtherScore]))

    )

VAR __SUM_XY =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(

            SUM('STEmployeeData'[WillingnessScore])

                * SUM('STEmployeeData'[OtherScore]) * 1.

        )

    )

VAR __SUM_X2 =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('STEmployeeData'[WillingnessScore]) ^ 2)

    )

VAR __SUM_Y2 =

    SUMX(

        KEEPFILTERS(__CORRELATION_TABLE),

        CALCULATE(SUM('STEmployeeData'[OtherScore]) ^ 2)

    )

RETURN

    DIVIDE(

        __COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,

        SQRT(

            (__COUNT * __SUM_X2 - __SUM_X ^ 2)

                * (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)

        )

    )

[rajendraongole1]

Hi @bdue - Can you check the below measure for correlations at the question level.

QuestionCorrelation =
VAR WillingnessToRecommendScore =
AVERAGEX(
FILTER('STEmployeeData', 'STEmployeeData'[Question] = "Willingness to Recommend"),
'STEmployeeData'[Score]
)
RETURN
CALCULATE(
DIVIDE(
SUMX(
SUMMARIZE('STEmployeeData', 'STEmployeeData'[Question]),
(AVERAGEX(FILTER('STEmployeeData', 'STEmployeeData'[Question] <> "Willingness to Recommend"), 'STEmployeeData'[Score]) - WillingnessToRecommendScore)
* ('STEmployeeData'[Score] - WillingnessToRecommendScore)
),
SQRT(
SUMX(
SUMMARIZE('STEmployeeData', 'STEmployeeData'[Question]),
(AVERAGEX(FILTER('STEmployeeData', 'STEmployeeData'[Question] <> "Willingness to Recommend"), 'STEmployeeData'[Score]) - WillingnessToRecommendScore) ^ 2
)
* SUMX(
SUMMARIZE('STEmployeeData', 'STEmployeeData'[Question]),
('STEmployeeData'[Score] - WillingnessToRecommendScore) ^ 2
)
)
),
FILTER('STEmployeeData', 'STEmployeeData'[Question] <> "Willingness to Recommend")
)

Hope it works in your case, check it

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[bdue]

Thank you @rajendraongole1 !  I am trying to update the measure with the correct field names, and I think I have resolved all of the errors but one.  The actual name of "score" is [AnswerNumeric], so I replaced that, but two of the instances give me a "cannot find name" error, I presume because AnswerNumeric is a table value, not a measure? Highlighted below are the errors I am getting.

I am also curious if this DAX can be generated from the quick measure, or if the quick measure logic just won't yield what you have modified?

VoEQuestionCorrelation =

VAR WillingnessToRecommendScore =

AVERAGEX(

FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] = "Q260_006_9"),

'STEmployeeData'[AnswerNumeric]

)

RETURN

CALCULATE(

DIVIDE(

SUMX(

SUMMARIZE('STEmployeeData', 'STEmployeeData'[QuestionCode]),

(AVERAGEX(FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] <> "Q260_006_9"), 'STEmployeeData'[AnswerNumeric]) - WillingnessToRecommendScore)

* ('STEmployeeData'[AnswerNumeric] - WillingnessToRecommendScore)

),

SQRT(

SUMX(

SUMMARIZE('STEmployeeData', 'STEmployeeData'[QuestionCode]),

(AVERAGEX(FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] <> "Q260_006_9"), 'STEmployeeData'[AnswerNumeric]) - WillingnessToRecommendScore) ^ 2

)

* SUMX(

SUMMARIZE('STEmployeeData', 'STEmployeeData'[QuestionCode]),

('STEmployeeData'[AnswerNumeric] - WillingnessToRecommendScore) ^ 2

)

)

),

FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] <> "Q260_006_9")

)

[hackcrr]

Hi, @bdue 

To calculate the correlation between each survey question's scores and the "willingness to recommend" scores, you need a measure that will work correctly at the row level. The issue with the quick measure is that it aggregates the data before calculating the correlation, which is why you only get a result in the table total.

Create a Measure for the Willingness to Recommend Score. This measure will calculate the average score for the "willingness to recommend" question.

WillingnessToRecommendScore = 
CALCULATE(
    AVERAGE('STEmployeeData'[AnswerNumeric]),
    'STEmployeeData'[QuestionCode] = "Q260_006_9"
)

Create a Measure for the Correlation Calculation. This measure will calculate the correlation between each question's scores and the "willingness to recommend" scores.

VoEQuestionCorrelation = 
VAR AllSurveyIDs = VALUES('STEmployeeData'[SurveyID])
VAR WillingnessToRecommend = 
    CALCULATETABLE(
        FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] = "Q260_006_9"),
        AllSurveyIDs
    )
VAR CurrentQuestionScores = 
    CALCULATETABLE(
        FILTER('STEmployeeData', 'STEmployeeData'[QuestionCode] <> "Q260_006_9"),
        AllSurveyIDs
    )
VAR WillingnessToRecommendAvg = AVERAGEX(WillingnessToRecommend, 'STEmployeeData'[AnswerNumeric])
VAR CurrentQuestionAvg = AVERAGEX(CurrentQuestionScores, 'STEmployeeData'[AnswerNumeric])
VAR Numerator = 
    SUMX(
        CurrentQuestionScores,
        ('STEmployeeData'[AnswerNumeric] - CurrentQuestionAvg) * 
        (CALCULATE(AVERAGE('STEmployeeData'[AnswerNumeric]), 'STEmployeeData'[QuestionCode] = "Q260_006_9") - WillingnessToRecommendAvg)
    )
VAR Denominator = 
    SQRT(
        SUMX(
            CurrentQuestionScores,
            ('STEmployeeData'[AnswerNumeric] - CurrentQuestionAvg) ^ 2
        ) *
        SUMX(
            WillingnessToRecommend,
            (CALCULATE(AVERAGE('STEmployeeData'[AnswerNumeric]), 'STEmployeeData'[QuestionCode] = "Q260_006_9") - WillingnessToRecommendAvg) ^ 2
        )
    )
RETURN
    DIVIDE(Numerator, Denominator)

Add the VoEQuestionCorrelation measure to your table visual. Ensure the table is filtered to show data for each question and not just the total.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[bdue]

Thank you @hackcrr!  I appreciate the context around why the quick measure wasn't working.  That said, I seem to be getting the same kind of result with only a summary correlation, not a row level correlation with the solution you provided.  The correlation value is also unusual (not between -1 and 1).

Here is a screenshot of the table I am seeing, with question codes (the one I am comparing the others to is marked), and the numeric values and blanks in the new column with the measure you wrote. 

And just in case I was mis-reading your note about filtering the visual, I filtered to just the willingness to recommend question and one other, and no matter what other question I pick, I get a zero correlation....