Finding high-impact issues with cluster style and weighted average

By using both size and color, heat maps make it easy to identify individual items that are both important (size) and require urgent attention (color). For example, if we are looking at a portfolio of IT projects, as in the picture below, we need only look for large red or yellow rectangles to identify projects with large budgets that are significantly behind schedule.

If we had unlimited resources, we would use those resources on every project that runs into trouble, but of course no company has that luxury. We must identify the critical ones, accounting for both the resources that are being expended and the value that the project promises. Large-budget projects are obviously important, but we may also need to take into account the impact of individual projects, for example, on business progress toward strategic priorities.

Let’s see how we can do this.

The heat map below shows a set of projects grouped by project manager, then by strategic focus area. The size is mapped to the budget, and color represents the cost overrun. Here green projects are on track, while red ones are significantly over budget. Although not visible here, each project also has a Business Value Index, a measure of the value of the project for the strategic area to which it belongs.

Projects heat map with window style

The first thing we’ll do is turn on the Cluster style, which colors the borders of each group using the aggregated values of the field mapped to color. Cluster style button The first time you do this, you’ll get a Best Practices dialog: “The Cluster Style works best with an `Average’ aggregation for the color field. Do you want to switch to this aggregation now?”. Click yes, since otherwise most values will fall outside the color range.

Projects heat map with cluster style

Now the borders of the groups are colored according to the average cost overrun of all the projects within each strategy and project manager group. The problem is that a straight average doesn’t answer the question of how project problems impact business value. We could try weighting by budget, but that just duplicates information that’s already being conveyed. What we really want is to account for business impact directly.

A simple way to do this is to change the aggregation (rollup) formula for the color field to incorporate the business value index. In the Data menu, select Fields to bring up the Configure Fields dialog, which allows you to change the format and rollup formula for every column in your data set.

Configure fields - setting rollup formula

We’ll pick Weighted Average, then set Weighted By to the Business Value Index. Weighted Average is similar to Average, but instead of each data point contributing equally to the final average, its contribution is weighted based on the value of the Business Value Index.

The resulting map gives us interesting information that is not obvious either from the individual project information or from the average cost overrun for each group. While some conclusions are unsurprising, we learn that the impact of the End-User Technologies project that Hank oversees outweighs that of at least four other red projects with larger budgets.  This is particularly surprising in the case of the top-right group that Mary is handling – the large red rectangle would lead us to believe that we should prioritize that project over Hanks when, in fact, the opposite appears to be the case. While we may choose to focus resources on some of the largest projects to get them back on track, this analysis allows us to target resources at the next level much more effectively to ensure that our efforts have maximum business impact.

Projects with cluster style and weighted average

If you would like to try working with this data set you can download the Excel file here.