Forum Discussion
Average of answer by question with multiple value types
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
Solution SageHi 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
Hi LivioLanzo
Thanks and that makes sense - I should be able to figure out how to do that, part of my learning journey :-)
One follow-up question on data modelling in this respect:
There are three question types in my survey of which likert is one. Would you recommend having three different fact tables (one for the answers belonging to each question type) or instead to create three columns - one for each question type - and just have one fact table with a lot of null values?
I am using the answer table as my fact table and questions and respondents as dimension tables. Does that make sense?
Kind regards,
Max
- LivioLanzo
Hi Max_Kloosterman,
if you want to work out the averages as done by your previous example then I would say you need a separate fact table for the Questions LIKERT because for these questions the answers are numeric and for the other questions they are not, therefore you couldn't be averaging strings.
Generally speaking though, you would wanna count how many people gave a certain answer or how people responded to a specific question, therefore you could have everything within one fact table and you wouldn't need to have three columns per each question type. The question type Foreign Key can be in just one column and would be linked to the questions dimensions.
And you can make even more improvements by coming to this final model:
Thanks again LivioLanzo and I am currenlty using the proposed setup to make the report work.
I do run into one problem though with this setup though.
One question asks to select from a multiple choice list:
For example: choose the role that closes fits your own from the list below:
- Director
- SVP
- VP
- Employee of the Month
- Janitor
When no respondent selects janitor: then how do I show in a graph that we have no janitors using the proposed setup? since it was never selected it would not be part of the answer table correct?
- LivioLanzo
Hi LivioLanzo,
Not yet and your suggestion got me thinking about a solution that might work in this specific case. I will work on figuring out what might work tonight.
The thing is that the connector I am using to get data from surveymonkey is giving me two tables:
One table with all questions - essentially the questionnaire
One table with all responses - only the ones that have been selected.
I think I will try the following steps to get to where i need to be:
1. Import both tables from surveymonkey
2. join them into one table - which would indeed give me the choices that have never been selected as lines in the table with no respondents against them.
3. use that base table to create the structure you described earlier - than it might work.