grouping data in a bar chart

Nannette Huber

I'm trying to do a weekly report in a dashboard. One of my widgets is a bar chart where I want to group multiple rows of data so it shows as assigned to one person. Right now, I'm getting multiple single bars all assigned to one person. Is there a way to aggregate or group data?

Paul Newcome

Are you able to mock up an example for a screenshot maybe in a different program or hand drawn or something so that we can visualize exactly what you mean?

Nannette Huber

My data taken from two different grids and put into a report where I filter by date. I'll eventually have 8 grids feeding into this report. But for now, I just have some "play" data in there.

This is what I get when I take the data in the report and use it to make a bar graph in a dashboard.

I want it to do this where the data is aggregated and there is ONE bar for each person, not multiple.

Paul Newcome

You will need to pull these counts into a separate sheet so that each person only has one line with their totals listed. There are a number of ways to do this.

You could write out a series of COUNTIFS for each sheet and add them together.

=COUNTIFS({Sheet 1 Name Column}, [Name Column]@row, {Sheet 1 Date Column}, date_criteria) + COUNTIFS({Sheet 2 Name Column}, [Name Column]@row, {Sheet 2 Date Column}, date_criteria)

Or you could pull these metrics into hidden columns on each sheet, use cell linking to pull the per sheet counts into a "rollup" sheet, then use a SUMIFS to add together all of the counts for each individual.

Nannette Huber

Thanks Paul.

So there's no way to get the graphs to aggregate. <sigh> That's what I want! <insert whiney voice here>

I'll give your solution a go. I know I was trying the SUMIFS yesterday but wasn't very successful with it. Part of the problem is that I have a few changing filter criteria that drive the report. And I'm handing this build off to someone else so it needs to be super easy to run the reports and not have the person changing the COUNTIFS or SUMIFS formulas.

Paul Newcome

Feel free to Submit a Product Enhancement Request.

In the meantime, I would be happy to help you set something up to make it easier on the end user.

Is it just one person that would be using it, or would there be multiple users that would be changing the filter criteria?

Nannette Huber

Thanks, Paul, that's super helpful of you! I'm gonna tackle this again late this afternoon and see if I can get something working. If I can't, I might take you up on your super generous offer.

Paul Newcome

Happy to help. 👍️

DDVL

Yes it's really silly that you cannot aggregative in the chart view and wasteful to have to make multiple summary sheets. Has anyone submitted a request for this feature? It's a massive waste of effort to have to create another sheet just to reshape data ESPECIALLY when Smartsheet doesn't seem to allow matching on multiple conditions.

Paul Newcome

This should now be possible with grouping and summaries in reports.

Adam Rasmussen

@Paul Newcome I'm trying to create a stacked bar chart that shows a bar for each project manager with a bar layer for each of their projects by status. I set up a report with nested groupings. My first grouping is by project manager. Then within each PM is a grouping by project status. I then added summaries by project status. The report provides to project counts by PM and counts for how many of their projects are at each status. When I create a stacked bar chart in my dashboard, I am only able to access the project count by the top level grouping of PM. Is there any way for me to use the count summaries in the second level grouping?

If not, how would you recommend that I get this information in a chart? I don't want to have a to create summary fields for each status for each PM.

Paul Newcome

@Adam Rasmussen I would suggest a separate Metrics sheets with each PM on their own row and then a column for each status. Then you would use a COUNTIFS with cross sheet references to populate the table based on a PM/status match.

Robin Herrick

I am trying to do a similar thing. The advantage of using a report is that it automatically creates the list of PMs so is dynamic to a changing team (or whatever the scenario is). I think the solution @Paul Newcome has offered requires the team to be entered manually. Of course the disadvantage of a report seems to be that you can't extract the relevant information for a chart, which seems a bit daft.

Robin Herrick

The flipside is that by creating a manual list you can retain categories with zero values.

Paul Newcome

@Robin Herrick You don't necessarily have to manually enter the entire team. I personally use a JOIN/DISTINCT to grab the full list (but only once per each name) and then parse it out down the column. Accounting for growth by prefilling some rows with the formula (outputting a blank if there are not that many names) allows for the flexibility of the report but with the ability to retain zero value categories.

Robin Herrick

Thanks @Paul Newcome. I haven't been using Smartsheet for long enough to know you can do SQL type commands.