Show Percentage from Sheet as Pie Chart in Dashboard

Brandon29 · 06/24/22

Hi-

I have a user on my team who is looking to show progress made against major tasks in his sheet as individual pie charts on a dashboard. For example, if "Milestone 1" is 50% complete, he'd like a pie chart on the dashboard that's just half filled in. Some of these tasks will just have an estimated percentage filled in, so they may not all be calculated by rolling up sub-task completion percentages. I'm not seeing a way to display a single cell from a sheet visually in this fashion, though. Is there a workaround perhaps that I'm not thinking of that would achieve this?

Genevieve P. · 06/27/22

Hi @Brandon29

You're correct that a Chart in a Dashboard cannot map a single value like this to show percents. You'll need to have the second half of the value (what is the remaining percent to make 100%) somewhere in your sheet as well.

What I would suggest doing is add a helper column in the source sheet, perhaps titled "Remaining Percent". Format this column to be a % type of column from the toolbar at the top. Then add a column formula that subtracts the current % Complete amount from 1. For example:

=1 - [% Complete]@row

If you have blank cells you want to ignore, you can add an IF statement in front:

=IF([% Complete]@row = "", "", 1 - [% Complete]@row)

Screen Shot 2022-06-27 at 10.46.30 AM.png

Then you can hide this column.

In your Chart, you'll want to select both cells (the original % Complete and the Remaining Percent) so you can display this as a Pie or Donut chart and change the colours, like so:

Screen Shot 2022-06-27 at 10.49.02 AM.png

Here's a webinar you may find useful: Visually Display Your Data with Dashboards

Cheers,

Genevieve

Genevieve P. · 06/27/22

Hi @Brandon29

You're correct that a Chart in a Dashboard cannot map a single value like this to show percents. You'll need to have the second half of the value (what is the remaining percent to make 100%) somewhere in your sheet as well.

What I would suggest doing is add a helper column in the source sheet, perhaps titled "Remaining Percent". Format this column to be a % type of column from the toolbar at the top. Then add a column formula that subtracts the current % Complete amount from 1. For example:

=1 - [% Complete]@row

If you have blank cells you want to ignore, you can add an IF statement in front:

=IF([% Complete]@row = "", "", 1 - [% Complete]@row)

Then you can hide this column.

In your Chart, you'll want to select both cells (the original % Complete and the Remaining Percent) so you can display this as a Pie or Donut chart and change the colours, like so:

Here's a webinar you may find useful: Visually Display Your Data with Dashboards

Cheers,

Genevieve

Brandon29 · 06/27/22

Thank you, Genevieve! That's an easy, elegant solution that my user is happy with. I appreciate your help!

Genevieve P. · 06/28/22

I'm glad to hear it! Thanks for following up 🙂

maltaee · 08/11/23

I followed the same steps, but I couldn't show the semicircle chart or even a pie.

Genevieve P. · 08/14/23

Hi @maltaee

To show a pie chart like the one I set up, you'd want to select two cells as the Range for the chart: the 0% and the one to the right, 100%

Then for the next chart you'd select the two cells down one row: 100% and 0%

And so on. Does that make sense? The explanation above is one-chart-per-row

maltaee · 08/14/23

Thanks, Genevieve.

Make sense. I have to figure out another way to visualize all statuses in one chart as each row represents a department or portion of the overall.

Show Percentage from Sheet as Pie Chart in Dashboard

Best Answer

Answers

Categories