Charting Text Dropdown List

maltaee

Hello,

I want to visualize the 4 options in a chart showing the percentage of each one. Also, I would like to show the same percentage for the 4 options within the sheet. I would like to know if one formula is an option or if I must create a column per option.

Thanks in advance.

Nick Korna

Hi @maltaee,

This is very much doable.

One way to do it is to use sheet summaries, then generate a sheet summary report which can then be used to create the chart in a dashboard.

Your summaries would be like this (using Zero as an example, change the word for the relevant thing matching each):

=(COUNTIF(Outcome:Outcome, "Zero") / COUNT(Outcome:Outcome)) * 100

If you want to be able to see the percentages within the sheet itself without needing the summary tab open:

=IF(Outcome@row <> "", (COUNTIF(Outcome:Outcome, Outcome@row) / COUNT(Outcome:Outcome)), "")

This just stops any blank rows showing up with a result, but lets the others appear.

You can then use a sheet summary report to take the data from your sheet, followed by using this in a dashboard widget.

Sample data:

Sheet summary report:

Chart widget in dashboard:

Obviously you can change the chart round to a different type and formatting, but hopefully this should give you an idea of how it could be done to get the outcome you're after.

If you've any questions on anything specific or I've misunderstood something, just post and we can work through it. Hopefully this helps though! 😊

maltaee

Hi Nick,

This is very helpful, and I missed the sheet summary completely.

I'm doing this for 30 goals, so 4 statuses for each goal. I guess the sheet summary report won't work as it is listed as one line.

One option is to rename each 4 states in the sheet summary to reflect the goal and then one can look at the sheet summary tab whenever needed e.g. Goal 1 Outcome - Zero, Goal 1 Outcome - Check, ... Goal 2 Outcome - Zero, etc. I still see it as too much to look for.

The other option is I created a helper column in the sheet and copied the formula from the Sheet Summary. And then created a new sheet and cell-link that to the source sheet. I will have all goals outcomes in one sheet, and I can create a chart for each goal.

Does that make sense, or might there be another efficient way?

Thank you,