Row Level Aggregation

MrAndrews — Tue, 21 Dec 2021 21:56:36 GMT

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!

Re: Row Level Aggregation

MrAndrews — Thu, 15 Apr 2021 09:54:45 GMT

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 )

)

topic Re: Row Level Aggregation in App Development

Row Level Aggregation

Re: Row Level Aggregation