cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leont3
Helper II
Helper II

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
Super User
Super User

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

3 REPLIES 3
DataZoe
Super User
Super User

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.