2 Replies Latest reply: May 9, 2010 10:47 PM by Matt Sweeney RSS

    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'

      error loading image

      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?