Pie Chart - Summarized Columns in a Sheet Summary Report

NBW501

Hi! I am trying to create pie chart from the summarized columns in a Sheet Summary Report. It allows me to create other types of charts with the data from the columns, but not specifically from the totaled summaries in each column.

See image below: I would like a pie chart with 2, 22, 3, 2 from the summary row in each column. Is this possible?

Totals.JPG

NBW501

A view of the image would be helpful :)

Genevieve P.

Hi @NBW501

The Summary data from a Report can only be used as a source for the Chart Widget if it's the first Grouping of the Summary.

This means that the top Total line cannot be used as the source, nor can a second or third Grouping.

In order to map this data you would need at least one Grouping applied. If you simply need the total, I would recommend ensuring that each row has an identical value on it (e.g. "Milestone") and then Group by the identical value! Once you have a first layer of Grouping/Summary, it will have a repeat row with the same data points that can now be mapped in a Chart.

See the original announcement post here: Create a chart from reports with grouping and summary

Cheers,

Genevieve

NBW501

Hi! Yes, this makes sense; however, I don't think it solves my issue...I posed a different question below to see if there's a different solution?

I am looking to create a pie chart that shows the total number of milestones on track, in progress, not started, etc. across multiple 20 sheets. I have all the same formulas in the Sheet Summary tab across all the sheets, and they are pulled into one report. Is it possible to create a single pie chart to represent these across al the plans?

Thanks for your help!

Genevieve P.

Hi @NBW501

In both cases I would recommend the following solution:

• Add a helper Summary Field that has identical data in each of your sheets.
• Create a Sheet Summary Report to bring all your data in one place.
• Group that Report by the identical Summary Field, so that you have one Grouping
• Summarize the columns/fields you want totals of

Then since your Report has one layer of both Grouping and Summary, the Chart Widget can read that second line beneath the Total.

Here's an example:

Notice how I have both a "Total" row at the top of the Report and a Grouped section? This means that the Chart can now read the first Grouping (which contains the exact same data):

Cheers!

Genevieve

NBW501

Genevieve, thank you SO MUCH! I really appreciate the help - it's exactly what I needed!

Genevieve P.

Wonderful! I'm glad I could help 🙂

abaker

Hi Genevieve P.

I know this is an old post but I'm following this same guidance for the same ask. My report setup is identical to your example; however, the Pie Chart option isn't available for my dashboard for this report. I noticed in your screen shot of your Pie Chart the data source is a sheet not a report. Perhaps the icons have changed since this original post. Yours is showing the sheet icon, not report. I'm wondering what I might be missing.

Genevieve P.

Hi @abaker

Can you provide screen captures of your Report set-up? (Blocking out sensitive data)

abaker

Hi @Genevieve P. here is a screenshot of the report:

Genevieve P.

Hi @abaker

Thank you! And does your Chart select "all columns" as well?

abaker

Yes it does, here is a screenshot of the dashboard chart setup.

Genevieve P.

Hi @abaker

I spent quite a while looking at your settings and duplicating what I thought was the "same" report, unable to replicate what you're seeing… and then I realized that your Report numbers are negative!

Pie and Donut charts can only report on positive numbers. A Column Chart can show a combination of Positive and Negative, above and below a line:

But if I changed this same chart to a Pie Chart, it only reports the positive numbers. It cannot create calculations with negative or 0.

With negative numbers, you could do a Stacked Bar or Stacked Column instead, if that helps.

Cheers,
Genevieve