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

# Table calculation question (nested? partitioning/addressing?)

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

 Market Coffee Espresso Herbal Tea Tea Grand Total Central 26.06% 22.52% 25.61% 25.80% 100.00% East 31.72% 27.10% 23.16% 18.02% 100.00% South 32.00% 43.29% 26.55% 26.53% 100.00% West 21.25% 25.68% 26.55% 26.53% 100.00% Grand Total 26.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

• ###### 1. Re: Table calculation question (nested? partitioning/addressing?)

Susan:

Have you checked this video link by Joe Mako and Matt LuttonJonathan Drummey something similar and hope this helps; Re: difference in % from total %

Attached workbook with same way as Joe solved it.

..kk

• ###### 2. Re: Table calculation question (nested? partitioning/addressing?)

You did well -- I tried to work it out myself earlier and got close but did not complete it.  Kudos KK!

• ###### 3. Re: Table calculation question (nested? partitioning/addressing?)

Thanks so much KK!

Yes, this is exactly what I was after, and thanks to Joe Mako as well.  A videa walk-through no less.

Susan