The issue here is that you are combining an aggregate function (sum of ebit / sum of net revenue) with a non-aggregate result ('meeting expectations'). What I would recommend would be setting up a function that sums EBIT YTD, and Net Revenue YTD separate from this calculation, and calling these functions within the nested IF.
If you would like to attach a copy of your workbook, I can try to work through the IF statement if my explanation was unclear.
There's a good chance that all you need to do is wrap your [Agency Name] field in a MAX(), MIN(), or ATTR() function. It may seem like a weird solution, but the error you're getting reflects the stubborn reality of working with software logic. You are bumping into a general rule that states you can't combine aggregate and non-aggregate data, and in this case you are aggregating some fields (taking the SUM of some metrics) while not aggregating another field (Agency Name) within the formula.
Wrapping [Agency Name] in MAX(), MIN(), or ATTR() won't actually change anything except that the aggregate/non-aggregate error will no longer be triggered because now ALL fields in the formula are "aggregated", technically. Since the calculations are evaluated at the row level, you shouldn't run into any trouble because the MAX() or MIN() of a single text value is... that text value! And since any given row only has one Agency Name associated with it, you should be good to go.
Hope that helps,
1 of 1 people found this helpful
Try using either MIN([Agency Name]) or Attr([Agency Name])
they will not change your results just meet the requirement for an aggregate
Let me know if this helps