I need help to solve a basic issue with Google Data Studio and I'm not able to solve it.
This is my situation:
1. I have a form which sends the result to a Google Spreadsheet
2. In the form there is a question with multiple choices and the results appear in one cell comma-separated.
3. I prepared a second tab in the Spreadsheet with all the possible choices.
4. I'm using Google Data Studio to show the results in a simple table.
5. So I blended the data from the second tab with all the options and a second data source with the actual results.
6. Now I want to see in the first column of the table all the possible choices from the second tab and in the right column the total amount it appeared in all responses from the form, including the ones with multiple answers in one cell.
As you can see in provided Screenshot I already managed to show all the possible choices in the first column, but the result of the count is not counting the cells with multiple answers.
I'm trying the formula with regexp_match to count.
I would prefer not to make any changes in the spreadsheet, because the response came in automatically and I don't want to update the Sheet everytime someone fills up the form.
Who can help me?
About the recuiterMember since Mar 14, 2020 Subarta Das
from Tunapuna/Piarco, Trinidad and Tobago