Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
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. 

Anonymous
Not applicable

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).  

 

@Anonymous 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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors