cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Max_Kloosterman
Frequent Visitor

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

1 ACCEPTED SOLUTION

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

15 REPLIES 15
LivioLanzo
Solution Sage
Solution Sage

Hello @Max_Kloosterman

 

would you be able to post a sample of your data and expected results?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi Livio,

 

Sure!

 

example from Answers Table: 

2018-11-26_14-27-55.jpg

 

 

Example from questions table:

 

2018-11-26_14-34-24.jpg

 

Example from respondents table:

 

2018-11-26_14-35-46.jpg

 

Expected Results

 

A table I can base various graphs on that looks like:

 

2018-11-26_14-46-55.jpg

 

 

Kind regards,

 

Max

 

Hi again @Max_Kloosterman

 

unless I am missing something, I think we're missing a table telling us what's the correct answer per question. Otherwise based on what the score would be calculated?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo

 

There is no correct answer. The statements are scored disagree completely (1) to agree completely (5).

 

Hope that clarifies,

 

Kind regards,

 

Max

Hi @Max_Kloosterman,

 

What about the questions which are not LIKERT? The ones with OPEN Text and YN, should they not be considered?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

 

sorry for my late reply! 

 

The open questions can be ignored, the Y/N questions as well. The likert questions are the basis for a comparison between respondents / groups of respondents.

 

This is probably where I stranded, I couldn't get a measure calculated on the column containing the likert scores (as well as the other scores) to work.

 

Kind regards,

 

Max

Hi @LivioLanzo

 

Thanks for your help and questions so far - hope the clarification helped?

 

Kind regards,

 

Max

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

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:

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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?

 

Hi @Max_Kloosterman

 

have you tried within your visual to allow for 'Show Items with No Data'?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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. 

Thanks @Max_Kloosterman

 

Could you post some data which can be copy pasted easily?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo, thanks for your help!

 

I've put an example comparable to the real data in an excel file on dropbox. Hope that helps! 

 

https://www.dropbox.com/s/ahqosw5ew6kvpid/Example_Data.xlsx?dl=0

 

Kind regards,

 

Max

 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.