Correlation calculation between rows within a table

[senthil9324]

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.

[senthil9324]

@Greg_Deckler 

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

[V-lianl-msft]

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.

[Greg_Deckler]

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