SSRS sum max by group
In both of the total textboxes, region and Grand Total, you can use the same expression:
=Sum(max(Fields!goal.Value, "LocationName"))
Because the expression is calculated in the current Scope of the textbox, at the Region Group level this will be aggregating the Max
goal value in all LocationName groups in the region, and at the Grand Total level this will be aggregating the Max
goal value in all LocationName groups in all regions.
You can see this in action. With data like:
(I've doubled up the rows to make it obvious if the end totals are correct)
and a simple table:
The same expression gives the correct result in both the Region and Grand Total Scope: