Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a pivot table with the following dimensions:
State
County
Contract #
It will have a few expressions. One is Losses which is simply sum(Loss).
In the data, the losses are split out by the state and county, but the premium isn't. I want to allocate the premium based on the Loss/Total Loss ratio. So if I have the following:
Contract State County Loss
A1 NY A 3000
A1 NY B 7000
and the contract has a premium of 5000 (that's stored in another table), I want the following pivot table:
State County Loss Premium
NY A 3000 1500
B 7000 3500
I have it working when the table is fully expanded. But when I collapse it to show totals by State, the numbers completely change.
Any idea on a formula to allocate the premium properly?
See if this is what you are looking for:
=(Sum(Loss)/Sum(total<State> aggr(distinct Sum(Loss), State, County)))*Sum(Premium)