Producing a chart on a dashboard to show resource capacity
I have created a report with information about task status and owner for a bunch of projects. I am trying to summarise this on the dashboard as a pie chart (ideally open tasks by owner); however, the chart shows the same owner multiple times in different colours because they have outstanding tasks for multiple projects. Is there a way to simply display open tasks (i.e. in progress, not started) for each person - so if there is a team of 4 people, it should just have 4 segments?
Best Answer
-
Hi @Tara Bush
The way to do this will depend on how your source data is configured for the chart widget. I'll outline 3 different ways you could configure your data in the Dashboard:
1. Multiple Pie Charts Per Person
Each owner would have their own Pie Chart that shows the different tasks they own and what status these tasks are in. (These are the ones on the far left of my screen capture below.)
2. Multiple Pie Charts Per Status
Each type of status would have its own Pie Chart to show how many each person owns in that status (the middle charts in the image below).
3. Total Pie Charts
You could either do a COUNTIF to summarize Total Projects that each user owns, regardless of status (bottom chart on the right), or you could break down the projects by User AND by Status.
This second chart sounds like what you already have set up, in which case I would suggest choosing a colour for each of your four owners and assigning different colour shades to each status so it's obvious to see who owns each section (the top chart on the right).
Let me know if this makes sense or if you'd like to see how I configured the Metric sheet I used to gather this data for each of the charts.
Here are some resources I used to help me build these:
Cheers!
Genevieve
Answers
-
Hi @Tara Bush
The way to do this will depend on how your source data is configured for the chart widget. I'll outline 3 different ways you could configure your data in the Dashboard:
1. Multiple Pie Charts Per Person
Each owner would have their own Pie Chart that shows the different tasks they own and what status these tasks are in. (These are the ones on the far left of my screen capture below.)
2. Multiple Pie Charts Per Status
Each type of status would have its own Pie Chart to show how many each person owns in that status (the middle charts in the image below).
3. Total Pie Charts
You could either do a COUNTIF to summarize Total Projects that each user owns, regardless of status (bottom chart on the right), or you could break down the projects by User AND by Status.
This second chart sounds like what you already have set up, in which case I would suggest choosing a colour for each of your four owners and assigning different colour shades to each status so it's obvious to see who owns each section (the top chart on the right).
Let me know if this makes sense or if you'd like to see how I configured the Metric sheet I used to gather this data for each of the charts.
Here are some resources I used to help me build these:
Cheers!
Genevieve
-
Thanks Genevieve - that's really helpful and given me some ideas on how to best present this data. Would it be possible to share the metric sheet? I have started to do countifs on open tasks per owner but would be good to see how the other charts were created?
Thanks,
Tara
-
Hi @Tara Bush
No problem at all! These are the different formats I used for the first two charts:
It's the same formula, I just grouped them differently depending on what chart I was using. Then when selecting the data I selected it by the groups/colours. Does that make sense?
Keep in mind that you will want to adjust the widget to only include the columns you need... so for the second chart I would select from Genevieve to the yellow number 2 (the three rows). Then I would exclude the "Project Status" column from the widget because I'll title the widget that name instead.
The formula I used is the following:
=COUNTIFS({Assigned To Column}, User@row, {Status Column}, [Project Status]@row)
You would need to adjust your column names for what's in your current sheet, and select different cross-sheet ranges {in these}.
Let me know if you need anything else!
Cheers,
Genevieve
