Ok - here is the challenge. We have a metric that is a percentage compliance per resource. If the numbers are between certain ranges they are either red, yellow or green. This is straightforward but for the roll up to the managers I don't want to average all of the underlying data to get an aggregate percentage as the individual results will get lost in the average of the averages. What I want is to calculate the manager performance against the kpi based on the number (count) of his resources that are in each bucket. The manager might have an average of averages that looks good if you aggregate but the he might have half of his resources in the red.
Example data
Resource 1 - 112% - Yellow
Resource 2 - 105% - Green
Resource 3 - 130% - Red
Resource 4 - 74% - Red
Resource 5 - 60% - Red
If I were to average these 5 I would get 96.2% - falling in the Green - but the details reflect the problems and needs to be reflected as 60% are red and 20% are yellow.
Ranges -
Green - from 90-110
Yellow - from 80-90 and 110 to 120
Red - 0-80 and 120 and above
We have a count of red, yellow and green at the resource level but are struggling how to roll up and aggregate at a manager level. This is for a large organization with lots of managers.
