Forum Discussion
Correlation calculation between rows within a table
I am working on survey related data & I want to calculate how one question influences all other questions asked to users.
Below is the sample data.
Survey id is unique for each person
Multiple questions are asked to each person
Score is between 0 - 100
| Survey Id | Date | Question | Score |
| 852973 | 3/25/2020 | How likely to use Dealership in future | 0 |
| 852973 | 3/25/2020 | Overall Delivery Experience | 20 |
| 852973 | 3/25/2020 | Delivered When Promised | 70 |
| 852973 | 3/25/2020 | How likely to use TCIF in future | 90 |
| 852973 | 3/25/2020 | Overall Sales Experience Satisfaction | 80 |
| 852973 | 3/25/2020 | Sales Personnel Fair and honest | 70 |
| 852973 | 3/25/2020 | Overall Sales Personnel Satisfaction | 20 |
| 852973 | 3/25/2020 | Sales Personnel Explanation of features and options | 60 |
| 852973 | 3/25/2020 | Overall TCIF Satisfaction | 0 |
| 852973 | 3/25/2020 | Sales Personnel Helpful and courteous | 70 |
| 852973 | 3/25/2020 | Sales Personnel Product knowledge | 10 |
| 852973 | 3/25/2020 | Sales Personnel Explanation of financing alternatives | 30 |
| 852973 | 3/25/2020 | Sales Personnel Explanation of warranty coverage | 60 |
| 852973 | 3/25/2020 | Delivery Personnel Specifications | 50 |
| 852973 | 3/25/2020 | Delivery Personnel Appearance and Condition | 0 |
| 852973 | 3/25/2020 | Delivery Personnel Installation | 0 |
| 852973 | 3/25/2020 | Delivery Personnel Explanation | 80 |
| 852973 | 3/25/2020 | Sales Personnel Product recommendations | 30 |
| 835566 | 7/16/2019 | Sales Personnel Fair and honest | 70 |
| 835566 | 7/16/2019 | Delivered When Promised | 80 |
| 835566 | 7/16/2019 | Delivery Personnel Specifications | 0 |
| 835566 | 7/16/2019 | Delivery Personnel Explanation | 0 |
| 835566 | 7/16/2019 | Overall Sales Personnel Satisfaction | 0 |
| 835566 | 7/16/2019 | Sales Personnel Helpful and courteous | 90 |
| 835566 | 7/16/2019 | Sales Personnel Product recommendations | 80 |
| 835566 | 7/16/2019 | Sales Personnel Explanation of financing alternatives | 10 |
| 835566 | 7/16/2019 | Delivery Personnel Appearance and Condition | 0 |
| 835566 | 7/16/2019 | Overall Sales Experience Satisfaction | 50 |
| 835566 | 7/16/2019 | Sales Personnel Explanation of warranty coverage | 10 |
| 835566 | 7/16/2019 | Sales Personnel Explanation of features and options | 0 |
| 835566 | 7/16/2019 | Overall Delivery Experience | 80 |
| 835566 | 7/16/2019 | Sales Personnel Product knowledge | 10 |
| 835566 | 7/16/2019 | Delivery Personnel Installation | 0 |
| 835566 | 7/16/2019 | How likely to use Dealership in future | 90 |
| 833785 | 7/11/2019 | Sales Personnel Product knowledge | 20 |
| 833785 | 7/11/2019 | Sales Personnel Explanation of financing alternatives | 60 |
| 833785 | 7/11/2019 | How likely to use Dealership in future | 60 |
Now, my requirement is, say I have in total asked 10 question to a user and I want these 10 question to be correlated again 5 (can increase or decrease in future) question among those 10 to see how one question's score is influencing the other question score.
Users will have a single selection ON slicer with 5 question, and they can select one of the question to see the correlation score with total 9 question excluding the selected question.
3 Replies
senthil9324 Are you talking correlation like this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Correlation-coefficient/m-p/196274#M21
Chapter 11 of DAX Cookbook has a bunch of statistics stuff. You can get the DAX here: https://github.com/gdeckler/DAXCookbook
Sorry, not sure what you mean by correlation.
Hi senthil9324 ,
It's not clear how to calculate the influences.
The options in the slicer increase or decrease,what you described need to be updated manually.
You could create an unrelated new table as the slicer.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Yes, I am looking for this kind of correlation coefficient only.
Let me further explain what my requirement is...
We are trying to find out how a particular question's score is influencing other question's score, for ex: if a user gives low score for a question like "Are you satisfied with salesperson's demo/explanation?" ... this should automatically reduce score for a question like "How satisfied are you with dealership in helping, buy you the product?"
With existing sample data, Say If I choose question "Overall Sales Experience Satisfaction" as a base question, then I want to correlate its score with all other questions.
As per above link,
Item is question,
Value X is Score of question
Value Y should be base question score, repeating for all question within that survey id's (which I want it to be dynamic based on certain per selected questions)
Then calculate Correlation coefficient for each question for selected duration using a date filter.
Below is the required format to calculate the Correlation coefficient and that has to be happen dyamically wheneven base question is changed.
Survey Id Date Question Score Overall Sales Experience Satisfaction 817852 1/23/2019 Delivered When Promised 90 80 817852 1/23/2019 Delivery Personnel Appearance and Condition 60 80 817852 1/23/2019 Delivery Personnel Explanation 0 80 817852 1/23/2019 Delivery Personnel Installation 30 80 817852 1/23/2019 Delivery Personnel Specifications 80 80 817852 1/23/2019 How likely to use Dealership in future 40 80 817852 1/23/2019 How likely to use TCIF in future 30 80 817852 1/23/2019 Overall Delivery Experience 40 80 817852 1/23/2019 Overall Sales Experience Satisfaction 80 80 817852 1/23/2019 Overall Sales Personnel Satisfaction 70 80 817852 1/23/2019 Overall TCIF Satisfaction 90 80 817852 1/23/2019 Sales Personnel Explanation of features and options 70 80 817852 1/23/2019 Sales Personnel Explanation of financing alternatives 40 80 817852 1/23/2019 Sales Personnel Explanation of warranty coverage 20 80 817852 1/23/2019 Sales Personnel Fair and honest 0 80 817852 1/23/2019 Sales Personnel Helpful and courteous 80 80 817852 1/23/2019 Sales Personnel Product knowledge 30 80 817852 1/23/2019 Sales Personnel Product recommendations 70 80 818467 1/2/2019 Delivered When Promised 50 70 818467 1/2/2019 Delivery Personnel Appearance and Condition 30 70 818467 1/2/2019 Delivery Personnel Explanation 0 70 818467 1/2/2019 Delivery Personnel Installation 40 70 818467 1/2/2019 Delivery Personnel Specifications 80 70 818467 1/2/2019 How likely to use Dealership in future 40 70 818467 1/2/2019 How likely to use TCIF in future 80 70 818467 1/2/2019 Overall Delivery Experience 50 70 818467 1/2/2019 Overall Sales Experience Satisfaction 70 70 818467 1/2/2019 Overall Sales Personnel Satisfaction 70 70 818467 1/2/2019 Overall TCIF Satisfaction 40 70 818467 1/2/2019 Sales Personnel Explanation of features and options 0 70 818467 1/2/2019 Sales Personnel Explanation of financing alternatives 20 70 818467 1/2/2019 Sales Personnel Explanation of warranty coverage 50 70 818467 1/2/2019 Sales Personnel Fair and honest 20 70 818467 1/2/2019 Sales Personnel Helpful and courteous 80 70 818467 1/2/2019 Sales Personnel Product knowledge 70 70 818467 1/2/2019 Sales Personnel Product recommendations 0 70 819074 3/7/2019 Delivery Personnel Appearance and Condition 70 30 819074 3/7/2019 Delivery Personnel Installation 50 30 819074 3/7/2019 Delivery Personnel Specifications 0 30 819074 3/7/2019 How likely to use Dealership in future 70 30 819074 3/7/2019 Overall Sales Personnel Satisfaction 30 30 819074 3/7/2019 Sales Personnel Explanation of features and options 30 30 819074 3/7/2019 Sales Personnel Explanation of financing alternatives 60 30 819074 3/7/2019 Sales Personnel Explanation of warranty coverage 30 30 819074 3/7/2019 Sales Personnel Fair and honest 60 30 819074 3/7/2019 Sales Personnel Helpful and courteous 90 30 819074 3/7/2019 Sales Personnel Product knowledge 90 30 819074 3/7/2019 Sales Personnel Product recommendations 20 30
