The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- How to set up correlation measure with row level r...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to set up correlation measure with row level results

07-12-2024
10:28 AM

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)

)

)

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-14-2024
03:31 AM

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!!

Proud to be a Super User! | |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-16-2024
12:59 PM

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

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2024
09:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-24-2024
12:40 PM

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....

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Check out the August 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

113 | |

78 | |

77 | |

43 | |

38 |

Top Kudoed Authors

User | Count |
---|---|

148 | |

116 | |

65 | |

64 | |

54 |