Forum Discussion
Showing Likert Scale data in one table
"Do i need to format my data a different way?" Yes
service extremely satisfied satisfied dissatisfied Extremely dissatisfied Service 1 1 23 4 7 Service 2 3 12 5 2 service 3 1 4 5 7 Powerbi can manipulate data in all sorts of ways. However, make it easy on yourself, get the data in a format that means you can concentrate on easily making/designing/altering the visualisations. Don't get to the report page in powerbi with long lists of text values that need to be manipulated differently for different visualisations
Powerbi works best with aggregations.
That picture you posted can't be anything like your real data. Individual responses to each service are all the same and there's one more response in each column than the one before. If i'm wrong, i apologise.
Try and get your data in the format i've shown. If you can't then
post some masked data (not a picture of it), i'll put it in a pbix and send a link back to you
I think I've found a decent solution. It's not a perfect approach, but here's what I did...
First, in Power Query I copied the query, Chose Columns to include only the ID and the Likert Items, and then unpivoted the Likert scale so that I had a zillion rows of Attribute/Value pairs. Then, I added a Conditional Column (cheating using Column from Examples) to give a numeric value to each of my values. In my case, Strongly Disagree = -2, Disagree = 1, Neither Agree nor Disagree = 0, Agree = 1, Strongly Agree = 2.
Loading this into the data model, Power BI auto-detected the ID as a key field and built the relationship between my main table and my "Likert Question" table.
To visualize, I just used a horizontal barchart using the Question for the axis and the "Response_Numeric" for the values (using the "Average" summarization), plus a custom X axis label (with a bunch of underscores, since PBI ignored multiple spaces). I also force-set the X axis so that the Min was -2 and the Max was 2 so that the scale stayed true to the feedback.
It doesn't give the full nuance that the native MS Forms tool does and it's not super-pretty, but it does show the gist of the data as you apply filters based on other demographics. Here's what it looked like (note that this was just a subset of my data, but it highlights how it looks):