You're asking a big request here!
Which part of the design are you struggling with?
Creating those reds and greens are pretty straightforward. Set up individual parameters for the various targets, and then create a calc for each metric that triggers from the parameters:
If ATTR([Team]) = 1 then
if SUM([whatever measure])>= [Team 1 Target for Metric 1] then "Green" else "Red" END
ELSEIF ATTR([Team]) = 2 then
(same for Team 2 Target for Metric 1] ...)
ELSEIF ... 3 ...
The next trick will be to roll up all the greens and reds for each metric. That will use a table calc that will run along the Team dimension:
WINDOW_SUM( IF ATTR([METRIC1 CALC]) = "Red" then 1 else 0 END )
And if the value of that calc > 0 then you have at least 1 "Red", so the overall light at the top for Metric1 will be Red. (This will probably be displayed as a separate sheet.)
I don't get the extra things at the top (94%, 6.5%...) Are they just averages of the specific metric targets? If so, that would be a separate sheet. (Or 3 sheets!)
Thanks for your answer. Essentially, I am struggling with Figure 1 (Top Level Target Metrics).
I am currently using a similar format you suggested (If any of the teams are Red, then the Metric 1/Metric 2/ Metric 3 is Red). There wasn't a problem setting targets at a team level.
However, what I would like to accomplish is the following:
I would like OVERALL targets set on top 94%, 6.5%... (Figure 1) for all the teams within that metric.
I would like to calculate the average of those different team metrics for an overall target (collectively) to compare against an overall Target.
I.e. All the Teams under Metric 1 Targets (Figure 2) should be averaged and compared with an Overall Target that is independently set (Figure 1). If this sum collectively reaches this target (i.e. 94%, 6.5%) then it would be GREEN, if not, then it would be RED.
That will take table calcs, and the details of what you need to do will depend on your application.
Can you upload a sample workbook?