2 Replies Latest reply on Apr 20, 2012 8:29 AM by Michael Nealey

    Difference from a total

    Michael Nealey

      I'm trying to calculate the difference of an aggregated calculation for a given dimension from the total of that calculation.

      My example shows 3 categories - Furniture, Office Supplies, Technology.

      Each has its own profit ratio for the 4 given years, 2009-2012.

      The total profit ratio across all 4 years (across all 3 categories) is also displayed - from the "show column grand totals" feature.

      What I am trying to do, and failing, is create a calculation that will give me the difference of the profit ratio for each category from the total profit ratio for the year...

      So...in 2009, where Furniture profit ratio is 4.20%, and the total profit ratio across the categories is 10.32%, my calculated difference for Furniture should be -6.12%.

       

      Any ideas?

        • 1. Re: Difference from a total
          Fionnuala Gibney

          Hi Michael,

           

          I broke this down into two steps (easier for me to understand), you can probably combine then if you wish, but they're using different types of table calculations, so maybe not? I'm sure someone will have a more elegant solution, but this works.

           

          1. Created a table calculation field called 'ProfitRatio-Totals' which (using 'Table (Across)' i.e. Order Date) just returns the totals for that year.

          i.e. TOTAL([Profit Ratio])

           

          2. Created another calculated field called 'ProfitRatioDifferences' - which uses 'Table (Down)',

          i.e. [Profit Ratio] - [ProfitRatio-Totals]

           

          Attached the sheet with this update.

           

          Fionnuala

          • 2. Re: Difference from a total
            Michael Nealey

            Hi Fionnuala -

            Thanks - I had actually figured this one out from another forum post...and your solution matched perfectly!

            Mike