Pivot table division at a collapsed/ expanded level

[#1]

I am applying an exchange rate to individual accounts (if they exist in an exception list) or other accounts (Should they not exist in the list).

One object is a straight table.

The formula for 'Adjusted TB AUD' is

Sum(Trans) / Rate

As this does not 'sum', I have set the column total mode as 'Sum of Rows'

If i modify the formula to Sum(Trans) / Sum(Rate) this creates an incorrect result.

The problem I have is when I want to group these accounts in a Pivot table, the Sum(Trans) / Rate formula does not work, and as rows are collapsed / expanded, the results change. This is because the rates 'lose' their relation to the account when grouped.

Is there a clear solution for this issue?

[johnw]

Maybe sum(Trans/Rate)?

[#3]

Sorry John, should have outlined this in my original post. This doesn't work either 😕