cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Data Modelling Survey Data - Likert Scale Question

Hi,

I am in the process of trying to present customer survey data at a restaurant and am struggling to see how I can model my data to reflect what I want shown below. Questions in the survey are grouped on a Likert Scale from Strongly Disagree to Strongly Agree and are then broadly categorised under certain areas for e.g. Dining Experience, Food Quality etc. 

 

I have a a flat file pulled from a Survey Platform where the Excel file looks like the following:

Response IDWhen thinking about your dining experience, to what extent do you agree or disagree with the following   
 I was greeted when I entered the restaurantI was told of the different food optionsA member of the staff checked up on me as I ateI had a fantastic overall dining experience
12370676107AgreeAgreeAgreeAgree
12369614146AgreeAgreeAgreeAgree
12369607028AgreeAgreeAgreeAgree
12369862544AgreeNeither Agree or DisagreeAgreeAgree
12370737258AgreeNeither Agree or DisagreeNeither Agree or DisagreeAgree
12369843672AgreeNeither Agree or DisagreeAgreeNeither Agree or Disagree
12370670509Strongly AgreeNeither Agree or DisagreeAgreeNeither Agree or Disagree
12369437538AgreeNeither Agree or DisagreeNeither Agree or DisagreeNeither Agree or Disagree
12369911732Neither Agree or DisagreeNeither Agree or DisagreeNeither Agree or DisagreeNeither Agree or Disagree
12370010988Strongly AgreeAgreeAgreeStrongly Agree
12369662924Strongly AgreeStrongly AgreeStrongly AgreeStrongly Agree

 

I would like the result to look like this in PowerBI:

Note: Weighted average is calculated with Strongly Disagree worth 1 point and Strongly Agree worth 5 points [I am hoping to link this up with a separate Dimension Table with each rating corresponding to a point.

1. Dining Experience
 Strongly DisagreeDisagreeNeither Agree or DisagreeAgreeStrongly AgreeWeighted Average
I was greeted when I entered the restaurant001 [9%]7 [64%]3[27%]4.18
I was told of the different food options006[55%]4[36%]1[9%]3.55
A member of the staff checked up on me as I ate003[27%]7 [64%]1 [9%]3.82
I had a fantastic overall dining experience004[36%]5[45%]2[18%]3.82

 

Much appreciate your help as always. 

 

1 ACCEPTED SOLUTION
DataZoe
Microsoft
Microsoft

@Anonymous You can do this by unpivoting your data when you bring it in, and creating a value for each of the reponses for the likert.   Then creating a custom measure that creates your table values from other measures utilizing inscope. To get the 0's to show up, you also need to create a table with your responses so it has a 0 context.

 

DataZoe_0-1612229034137.png


1. I imported the data from Sheet 1, then promoted the first row to headers, and added back in Response ID for your first column. I renamed Attribute to Question, and Value to Response. I duplicated Response and did replace text to change the words to the value from 1 to 5. I then made it a whole number. I named this table Survey.

2. Close and apply. then I created a "Response" table by simply clikcing "Enter data" and giving each of the response from Strongly Disagree to Strongly Agree. I also added a Rank column to order it correctly from 1 to 5.

3. I sorted Response in the Response table by the Rank column. Made sure there was a relationship between the two tables on Response.

4. I created the measures for Responses, Responses %, and Weighted Average:
Responses =
DISTINCTCOUNT ( Survey[Response ID] ) + 0

Response % =
DIVIDE (
    [Responses],
    CALCULATE ( [Responses], ALLEXCEPT ( Survey, Survey[Question] ) )
)

 

Weighted Average =
AVERAGE ( Survey[Likert] )

 

4. I created the Custom Table Values measure that will combine them all appropriately:
Custom Table Values =
SWITCH (
    TRUE (),
    ISINSCOPE ( Response[Response] ),
        [Responses]
            IF ( [Responses] = 0BLANK ()" [" & FORMAT ( [Response %], "0%" ) & "]" ),
    [Weighted Average]
)

 

5. I added a matrix to the page, and put the Question from the Survey table on Rows, the Response from the Response table on the columns, and the Custom Table Values measure I created in Values. I also did some custom formatting on the background color (on the Weighted Average measure) to color the columns.

 

Hope that helps!

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
DataZoe
Microsoft
Microsoft

@Anonymous You can do this by unpivoting your data when you bring it in, and creating a value for each of the reponses for the likert.   Then creating a custom measure that creates your table values from other measures utilizing inscope. To get the 0's to show up, you also need to create a table with your responses so it has a 0 context.

 

DataZoe_0-1612229034137.png


1. I imported the data from Sheet 1, then promoted the first row to headers, and added back in Response ID for your first column. I renamed Attribute to Question, and Value to Response. I duplicated Response and did replace text to change the words to the value from 1 to 5. I then made it a whole number. I named this table Survey.

2. Close and apply. then I created a "Response" table by simply clikcing "Enter data" and giving each of the response from Strongly Disagree to Strongly Agree. I also added a Rank column to order it correctly from 1 to 5.

3. I sorted Response in the Response table by the Rank column. Made sure there was a relationship between the two tables on Response.

4. I created the measures for Responses, Responses %, and Weighted Average:
Responses =
DISTINCTCOUNT ( Survey[Response ID] ) + 0

Response % =
DIVIDE (
    [Responses],
    CALCULATE ( [Responses], ALLEXCEPT ( Survey, Survey[Question] ) )
)

 

Weighted Average =
AVERAGE ( Survey[Likert] )

 

4. I created the Custom Table Values measure that will combine them all appropriately:
Custom Table Values =
SWITCH (
    TRUE (),
    ISINSCOPE ( Response[Response] ),
        [Responses]
            IF ( [Responses] = 0BLANK ()" [" & FORMAT ( [Response %], "0%" ) & "]" ),
    [Weighted Average]
)

 

5. I added a matrix to the page, and put the Question from the Survey table on Rows, the Response from the Response table on the columns, and the Custom Table Values measure I created in Values. I also did some custom formatting on the background color (on the Weighted Average measure) to color the columns.

 

Hope that helps!

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

For the weighted average what is your survey likert consist of? It just appears to be another form of ID but it differs from response ID so not too sure. 

In absence of the original poster thanking you, I am going to thank you - months later. 🙂

 

Zoe, you're an absolute legend for sharing the step by step instructions AND the downloadable pbix file.  I was able to replicate it (almost - still figuring out a few things).  

 

@ErinH I am so happy it's helpful to you! Let me know if you have any questions about it.  I was just doing more survey reports this week too.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors