3 Replies Latest reply on Dec 9, 2014 6:25 AM by Susan Oner

    Table calculation question (nested? partitioning/addressing?)

    Susan Oner

      Hi all,

       

      I'm looking for a way to show a relative comparison (index or %) by comparing to the average.  There is a sample notebook attached, the example is described in the caption of the worksheet.

       

      Basically, in a table with two dimensions, Quicktable calculations can show % of total using across one of them (i.e. Product Category):

       

      % of Net Sales

      MarketCoffeeEspressoHerbal TeaTeaGrand Total
      Central26.06%22.52%25.61%25.80%100.00%
      East31.72%27.10%23.16%18.02%100.00%
      South32.00%43.29%26.55%26.53%100.00%
      West21.25%25.68%26.55%26.53%100.00%
      Grand Total26.45%27.20%25.28%21.07%100.00%

       

      Now I'd like to compare each cell to the average for that product category.  I.e. The average % of net sales for coffee is 26.45%.  But in the South, it's 32.00%.  I'd like a table with calculation showing (32.00% - 26.45%) / 26.45% = 1.21 or 21% larger than average.

       

      Can I do this with table calcs and playing with addressing/partitioning?  I tried to create a calculation using the % of sales formula, then use that in the table but no go.  Any help would be very appreciated.

       

      Thank you,

      Susan