Data Modelling Survey Data - Likert Scale Question

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!