Pivot table division at a collapsed/ expanded level
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.