This example is contrived to clearly illustrate the problem and solution. Every block has expenses of $10 and a target of $20, except for the Support block, which has expenses of $40 and the same $20 target. The Bad status shows up easily in this table, but what happens when we remove the filters and look at the blocks tree? Everything looks really good! Expenses are well below targets:
Sure the overall numbers are good, but you don’t want a particular department’s overspending to become a problem later on down the line, so you want to tackle these issues immediately when they come up. Let’s create a MIN_SCORE cascading metric. The calculations are pretty much the same as the first SCORE example, except we’ll use the MIN_SCORE this time. Here’s the Actuals:
And the Target calculation can be exactly the same as before. (As a reminder, this formula just assures us we’re using the same dates as the Actuals series):
Now lets see what this looks like in the blocks tree. Our new metric is called “Find Bad Expenses” and if we display it in the tree:
Now we have a series of Bad blocks from the Support block all the way up to the company vision block. This allows someone to go from the root block and easily drill down to any problems blocks as they appear.