LinkedIn
Twitter
1 ACCEPTED SOLUTION
Ah, OK. So in this video, the starting data is like this:
| Response ID | Question 1 | Question 2 | Question 3 |
| 1 | Yes | No | No |
| 2 | Yes | Yes | Yes |
| 3 | Yes | No | Yes |
| 4 | Yes | No | No |
And then they change it into a table like this:
| Question number | Percent Yes Responses |
| 1 | 100% |
| 2 | 25% |
| 3 | 50% |
From there, the table can be visualized. The same is true in Looker studio - the second table could easily be visualized as a bar chart, but the first one cannot. So the question is how to transform the first table into the second table.
In the video, they use Power BI's transforming capabilities to unpivot the spreadsheet columns into rows. Looker Studio doesn't have such capabilities, so we'll need to apply some workarounds.
Step 1: For each question, create a percentage metric. The formula will look something like this: COUNT(IF(`Question 1`= "Yes",`Response ID`,null)) / COUNT(`Response ID`)
The numerator only counts responses where the user answered Yes for Question 1. The denominator counts all responses. Dividing these two gets us our percentage.
Step 2: Create a bar chart with all of the metrics you created in Step 1. Each bar will show up with a different color, because it's a different series.
Step 3: Apply styling for polish. In the example image here, I edited a few things:
- Setup: Set the format of each measure to percent
- Setup: Change the title of each question to be easier to read
- Style: Hide the Y axis title
- Style: Show data labels
I hope this helps you as a starting point!
Can you explain more how you'd like to present the data? You said you wanted to show multiple choice questions in a single graph with percentages.
Are you thinking of maybe a bar chart, where each bar is a question? And the number of responses are tallied up to show percentages on that bar? I'll share an example image of what I'm thinking of.
Ah, OK. So in this video, the starting data is like this:
| Response ID | Question 1 | Question 2 | Question 3 |
| 1 | Yes | No | No |
| 2 | Yes | Yes | Yes |
| 3 | Yes | No | Yes |
| 4 | Yes | No | No |
And then they change it into a table like this:
| Question number | Percent Yes Responses |
| 1 | 100% |
| 2 | 25% |
| 3 | 50% |
From there, the table can be visualized. The same is true in Looker studio - the second table could easily be visualized as a bar chart, but the first one cannot. So the question is how to transform the first table into the second table.
In the video, they use Power BI's transforming capabilities to unpivot the spreadsheet columns into rows. Looker Studio doesn't have such capabilities, so we'll need to apply some workarounds.
Step 1: For each question, create a percentage metric. The formula will look something like this: COUNT(IF(`Question 1`= "Yes",`Response ID`,null)) / COUNT(`Response ID`)
The numerator only counts responses where the user answered Yes for Question 1. The denominator counts all responses. Dividing these two gets us our percentage.
Step 2: Create a bar chart with all of the metrics you created in Step 1. Each bar will show up with a different color, because it's a different series.
Step 3: Apply styling for polish. In the example image here, I edited a few things:
- Setup: Set the format of each measure to percent
- Setup: Change the title of each question to be easier to read
- Style: Hide the Y axis title
- Style: Show data labels
I hope this helps you as a starting point!
