How to show active project phases in a dashboard bar chart

Jeff Casto

Hello,

We have multiple projects. We are trying to show the number of projects per active phase. Ie. 3 are in Strategy phase, 2 are in Creative Development, 4 are in Studio Production, etc…

1. Our projects all use a template project plan sheet that has tasks (children) grouped by phases (parents), dates and predecessors.
2. Each project task has a "% Complete" column. Using this, one can see what phases are not started, active or complete.
3. Currently, we have a bar chart on our portfolio dashboard that shows all of our projects are in all of the phases.

We'd like to actually see what projects are active in what phases.

Here is our current bar chart (it's not too helpful, just showing every project has every phase in it's project sheet)

This bar chart says it's pulling information from the portfolio metrics sheet here

The portfolio metrics shows it's pulling from the Summary Rollup

We already have a bar chart showing % complete by phase (this screenshot)

So, I'm not sure how to show a bar chart of the number of projects by active phase. Per the info in the % Complete per Phase (AVG) chart above we would have some number of projects in Strategy, some number of projects in Creative Concepting.

Another note is, if the previous phase is 100% or 0% complete, that phase would not show up as active.

If anyone has any thoughts about this, thank you!

Jeff Casto

I forgot one screenshot - here is a sample of our project sheet template

Thank you!

Matt Lynn-PCG

@Jeff Casto

So I've done this several times and there's prob a couple ways but the way I have done it is on your calc/metadata sheet do a formula that evaluates the % complete of each parent/phase row starting with the latest first. That will output the current phase of that (each) project, then roll that output to the summary rollup. Then do a countif per phase, and that's your chart source.

The original formula to evaluate per parent phase would be something similar to:

=if(% complete @ trafficking =1, then "Trafficking", if(% complete at Studio production =1, "Studio Production……

Obviously you have to change it to the proper references etc. If it's cross sheet you could do an index(match() for each one, BUT it would be easier to build and maintain if you just did a cell link to each of those cells onto the metric sheet, then your if statement would be much easier.

Jeff Casto

Hi @Matt Lynn-PCG , thank you so much for this answer. I have a slew of dumb questions, but will start with just a couple.

1. We use a "save as new" project folder template for all new projects. Should I create this formula in that project sheet "template" folder's Project Metadata sheet ?

2. If so, where in that sheet should I put the formula? Should I create a new column called something like "# of projects broken out by phase"? Then for that column add the formula?

3. Or do I make separate columns for each phase with that formula, but of course change the formulas based on the phase the formula is referring to? We already have separate phase columns which pull in % data from the project sheet. I'm not sure if this makes any difference to anything at all…Screenshot here:

Thanks for all the help!

Jeff Casto

Here's an even dumber follow up…Can I create a section on the Portfolio Metrics sheet that identifies the latest active phase per project? Something similar to the % Complete section?

Matt Lynn-PCG

@Jeff Casto

1.) Yes you should save this in a template folder and never have to create again.

2.) In the metadata you would create this formula and it would determine for that one project what the phase is. Then you'd have this value be rolled up to a summary level. Then at the summary level you'd have the above "even dumber question" sheet where you used that formula to query the summary column to analyze each of the projects to get your percentages. Then build your chart from there.

3.) You could do separate columns, roll that to some kind of report at the summary level and drive the chart from that. It wouldn't be the "right" way or the way I would prefer, but as with most things, there's always multiple answers.

It's hard to explain in words but the "right" way is to do all the calculations you want on the project metadata sheet and decide from there what are your summary rollup values and create a fast/easy way to roll them up either with cell link pasting (as range, not one by one) or using control center to do the work for you. Either way the structure is the same (mostly). Then everything is kept up to date/automatically with very little manual work.

Jeff Casto

Thanks again @Matt Lynn-PCG ! I'll kick this all around and will probably have some more questions after work on it.

Many thanks again!

Matt Lynn-PCG

@Jeff Casto If you hit a wall, maybe we can setup a 15min sync and I can show you more visually how it should be setup.

Jeff Casto

Hi @Matt Lynn-PCG

I'm getting an "Unparsable" note when I use this formula…Might you be able to point out where it's wrong?

=IF(% Complete@Strategy=1, then "Strategy"), IF(% Complete@Creative_Concepting=1, then "Creative Concepting")

I'm happy to do a 15 minute call, but don't want to wast your time…although this can be considered that!

Matt Lynn-PCG

@Jeff Casto Your % Complete@strategy doesn't look right. Formula references with spaces etc. must be in [] brackets.

It's prob supposed to be something like [% Complete]@row or something. If you click them it'll populate it for you.

https://scheduler.zoom.us/matt-lynn/15-min-help-sync