Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Data Modelling Survey Data - Likert Scale Question

‎02-01-2021 04:39 PM

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. 

 

Labels:
Message 1 of 5
4,754 Views
0
1 ACCEPTED SOLUTION
DataZoe
Microsoft Employee
Microsoft Employee

‎02-01-2021 05:25 PM

@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] = 0, BLANK (), " [" & 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

20210201-Survey.pbix
Message 2 of 5
4,722 Views
4 REPLIES 4
DataZoe
Microsoft Employee
Microsoft Employee

‎02-01-2021 05:25 PM

@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] = 0, BLANK (), " [" & 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/

20210201-Survey.pbix
Message 2 of 5
4,723 Views
knowledgesearch
Helper I
Helper I
In response to DataZoe

‎03-20-2023 08:16 AM

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. 

Message 5 of 5
3,009 Views
0
Anonymous
Not applicable
In response to DataZoe

‎10-28-2021 10:36 PM

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

 

Message 3 of 5
4,201 Views
DataZoe
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎10-29-2021 08:06 AM

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

Message 4 of 5
4,181 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

View All