Show Percentage from Sheet as Pie Chart in Dashboard
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?
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, Genevieve! That's an easy, elegant solution that my user is happy with. I appreciate your help!
-
I'm glad to hear it! Thanks for following up 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I followed the same steps, but I couldn't show the semicircle chart or even a pie.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives