How to create a pie chart based on a subset of a larger file

Miguel T

Dear Smartsheet community members,

I need some help in figuring out how to create a pie chart based on a subset of data, which will keep being updated over time. The source document has about 200 lines overall (as an example), and from these 200 lines, I want to be able to capture how many are "aligned" with our processes. For that purpose, I have an additional column in this source document with a drop down menu: "aligned" - Y or N. I would like to find a way to capture just the "aligned" lines as being the "100%" of my pie chart, and then capture how many of these "aligned" have been reached out in each quarter.

My main question is how can I capture the "aligned" as being the 100% of the pie chart taking into account that the number of "aligned" will keep changing, and how to capture the percentage from the aligned that have been reached out in each quarter. Any help or suggestions would be deeply appreciated, I've been going around in circles trying to figure out how to capture this, but all formulas and summary tables I try to use keep assuming the 100% as being the 200 lines from the original source file, and not just the ones showing as aligned. This allows me to understand how many of the 200 original lines have been reached out in each quarter, and how many of the reachouts have been done for aligned and not aligned, but what I really wanted to capture is how many within the aligned (only) have been reached out in each quarter. I hope this is not too confusing, but any help or tips would be deeply appreciated. Many thanks.

Paul Newcome

Are you able to provide some screenshots for reference?

Do you have a date column to compare for the quarter portion of this?

Miguel T

Hi Paul,

Thanks for getting back to me. The screenshot below is retrieved from the main file, and shows the alignment column on the left handside. The one in the middle shows the date as to when alignment has taken place, and on the right handside is a column which translates dates to quarters (these are from my company's fiscal year, so quarters will not necessarily reflect calendar quarters):

Using these 3 columns, I have created a reference table as per below:

This back end table has then allowed me to create a pie chart showing how many reachouts have been done on a specific quarter, however, it shows "aligned" vs. "non-aligned":

Whereas this is one of the insights I wanted to capture, my next objective would be to encapsulate only the aligned ones, and how many of these aligned ones have been reached out throughout each quarter, and that is where I am getting stuck. Any suggestion would be more than welcome!

Paul Newcome

I'm not sure I follow. The above is showing the aligned per quarter. Were you wanting that as just a stand-alone?

Miguel T

Hi Paul,

Think I found the resolution for this one already, basically I will need to create a summary table for each quarter. On this table, and taking Q1 as an example, I will create a countifs formula to account for how many are showing as aligned in the first column of my previous screenshot, with a 2nd range pointing to how many of the aligned have been aligned in Q1 only. This will give me the 100% of "aligned" in Q1. Then, in order to check how many of these aligned have been reached out on that same quarter, I will create a second column, and run a Countifs formula where I will account only for the "aligned", and whether or not there is a date belonging to Q1 where a reachout has taken place.