How do I create a pie chart showing the % of tasks in each stage?

Margaret Griffin

I've read through all the instructions on https://help.smartsheet.com/articles/2476691-compare-live-data-with-a-chart#faq, and I'm still unable to create a chart like the one shown in the example. It says that the chart widget only supports charting for number values, but the example shows percentages based on categories (text, not numbers). I just want to create a pie chart showing the percentage of tasks in each of our five stages. I'm on the Business Plan and I'm an Admin for the Dashboard. What am I missing?!

Joy Durham

Seems as though uou should be able to right click on the chart and tell it to show data labels

Margaret Griffin

The widget won't let me create the chart to begin with. When I select the data, I get the following error message: "Invalid Data. To create a chart you must use numeric data."

mitchbre

I'm also having this problem if anyone has a solution it would be much appreciated!

Julia B

Hi mgriffin - I definitely have felt your pain on this. I also struggled to find proper documentation to create these type of pie charts, but eventually figured it out!

Short-version: The dashboard's chart component cannot condense/analyze your text (or dropdown) source data. You must do this yourself in a separate sheet, using formulas. Then set your condensed/analyzed data set as the chart's source... and voila!

Long Version

(Screenshots / examples attached)

A. Source Sheet : You have a source sheet, with rows of data. One column is type Dropdown list and "Restrict to dropdown values only" = True. In my example, this is the Status column.

B. Summary Sheet: Create a new grid sheet. In the first column, put all the possible dropdown values (note: must be exact spelling!). In the next column, write a COUNTIF formula where:

- Range: references your source sheet, specifically the column of data with your dropdown list. 

- Criterion: the value in the first column

My formula looks like this: =COUNTIF({Client ABC - UAT Log Range 1}, Values@row)

(Note: I always use the "@row" feature to cutdown on formula calculation time)

C. Dashboard Component: On a Dashboard, add a Chart component. For your data set, select the summary data from your Summary Sheet. With this setup you can change the Dashboard Component type to a Pie chart and customize further as desired.

 

Enjoy

 

Steve Tornallyay

Thank you for the insight into how to make a pie chart.  It worked!!  After a few aggravating days of trying to find out how to make a pie chart, there is no clear step by step answer in the smartsheet guides. Your tips and screenshots were helpful.

Question. Can multiple functions live on the same summary page?

Jeffrey Seltzer

This is great. Thanks so much for sharing.

Mark Salamon

Julia B, thanks for that info. It's very surprising that the Dashboard support page does not mention that an intermediary sheet must be manually created with the aggregations. :(

In your example you used a status column, which likely has a handful of possible values. What if the drop down had a longer list (states or countries) or was a contact list that could have many values? With your clever approach you need to manually add a row in the intermediary sheet for each possible value. is there any way to make that more dynamic?

Andrew James

Thank you for the step by step walk through, still working out some kinks of my pie charts right now, some work, but others don't. I can make bar charts fine, but the options for the pie and donut charts are grey. Do you know why this may be?

Another issue I've come across, If you have issues with the numbers coming up as strings (from concatenation or other manipulation functions), the =VALUE() function will force the output to be formatted as a number.

Bayan

Hello everyone,

Here is a simpler way:

1- Add the required fields in Summary

2- Create a report and select the fields in Summary

3- Create a dashboard from that report

emkruczek

@Julia B Your walkthrough was such a massive help! I had been so frustrated trying to figure out how to integrate my data into a Pie Chart on my dashboard, but your instructions saved me. Much appreciated! 😀

mch_1

could also send it to google sheets; create the chart; publish it back to smartsheet dashboard

Frank Falco

You can also use a report using the group and summarise based on the item you want to graph.

This also allows you to incorporate filtering if required.

Connect the pie chart widget to the report rather than a sheet.