Forum Discussion
Average of answer by question with multiple value types
Hi,
I am analysing survey results and would like to average the answers for a subset of the questions based on their question type.
All answers are in a single column. Questiontypes are likert, open and yes/no, the values for likert type questions are numerical from 1 to 5. the answers to the open questions contain text.
I expect I should use the AVERAGEX function and CALCULATETABLE to run the averages on but lack the knowledge to calculate the right table so that it only includes likert type questions and averaging becomes possible. Help is much appreciated!
Kind regards,
Max
Structure of my dataset:
3 Tables which are crosslinked.
The Answers table contains:
AnswerID
QuestionID
RespondentID
Answer
The QuestionsTable contains:
QuestionID
QuestionType
Question
QuestionTopic
The Respondentstable contains:
OrganisationID
ResponentID
Organisation
OrganisationType
Hi Max_Kloosterman,
if you want to analyze just LIKERT questions, then you should import only these ones into Power BI. Do you know how to do that with Power Query?
Once it is imported it's just a matter of defining relationships and using dax AVERAGE function
15 Replies
- LivioLanzo
Solution Sage
Hi Livio,
Sure!
example from Answers Table:
Example from questions table:
Example from respondents table:
Expected Results
A table I can base various graphs on that looks like:
Kind regards,
Max
- LivioLanzo
