Row Level Aggregation

[MrAndrews]

Hi - I've been looking at this for ages, but I've hit a brick wall...

I'm building something which takes historical data, creates a ratio, then applies that ratio to future metrics.   The pivot then displays the future metrics.  I can get the pivot to behave perfectly well at a Totals level, but they also want to it doing the aggregation at a lower level... basically where the calculation happens on a row by row basis, and then gets summed afterwards. 

This Measure is fine...

SUM( {< DataType={Forecast} >}
  AGGR(

(SUM(Total {<Year={$(vYearChoiceBaseCommission)} >}[Underlying Claims]) / SUM(Total{<Year={$(vYearChoiceBaseCommission)} >}[GEP]))
*

(SUM( GEP_Forecast))
, Year)
)

This is absolutely fine for working out all of the totals after aggregation, but I can't work out how to do the aggregation at a row level ( there is a split of 'GBMS' by Year).  I have been messing about with Removing Total, adding Aggr in different places, but i've got total blindness now!    I can't shove this in the script as they want other flexibility.... it's a whole thing...

Sadly, I am unable to export an app, but i've attached dummy data and a screenshot... please help! 

I'd really appreciate any help!

[MrAndrews · Accepted solution]

Hi - Solved it - if anyone else comes across the same, and what I assume is a very specific issue, then I needed to sub total my inner set analysis, and tweak the outer.  New code is: 

SUM( {< DataType={Forecast} >}

AGGR(

(SUM( Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[Underlying Claims]) / SUM(Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[GEP]))

*

(SUM({<DataType={Forecast}>}GEP_Forecast))

, Year, GBM )

)