I think from your description here, you need to do the aggregation in the calculated field (as you would do for something like Margin %, in my retail world).
So your formula would be
This should then be correct and whatever level you are looking at.
Let me know if this doesn't do the trick (or make sense!).
Grays out entire worksheet.
Refuses to respond.
As this is now an AGGREGATE calculation, and you're previous calculation was ROW LEVEL and then AGGREGATED (as a SUM/AVG), you may need to remove the field and re-drag in (currently it's trying to do an AGGREGATE on and AGGREGATE, which I think is throwing the error). If that doesn't do the trick....Is there anyway you can post a bit of the data (anonymized if sensitive) so I can see what's going on. The 'Shape'/'Grain' of the data is important in knowing what the best approach is.
That was on the money. Thanks very much.