Trouble allocating in a pivot table

[danielact]

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?

[#2]

See if this is what you are looking for:

=(Sum(Loss)/Sum(total<State> aggr(distinct Sum(Loss), State, County)))*Sum(Premium)