Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. 

 

Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    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.

     




    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!

5 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    5 years ago

    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.

     




    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

      • DataZoe's avatar
        DataZoe
        Microsoft Employee
        4 years ago

        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.

    • knowledgesearch's avatar
      knowledgesearch
      Helper I
      3 years ago

      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. 

  • MichelleTW729's avatar
    MichelleTW729
    Helper I
    1 year ago

    Is anybody know how to change the right corner text be "Weighted Average" ? Cause my report shows "Total"