Custom Roll up and Drill down calculation for Excel Pivot table

Giganews Newsgroups
Subject: Custom Roll up and Drill down calculation for Excel Pivot table
Posted by:  Karen (ksabou…@oxfordinc.com)
Date: 30 Jan 2007

Greetings to all -

I am currently struggling with a method to apply a custom rollup
function for an Excel pivot table.  I am trying to calculate a sell
through % which is calculated as Sales $/(Sales $ + Ending Inv $).  I
want to be able to calculate this % at the weekly, monthly, and yearly
level.  I can successfully add a calculated field which works
perfectly when the user has WEEK on the pivot table.  However, if the
user wishes to remove week and see the Sell Thru % at the Monthly
level, then Excel naturally sums the Sell Thru % for each week in the
month.  This is not an accurate Monthly Sell Thru %.  When the user
pivots, I want to recalculate the sell thru %.  For example, when the
user wishes to see the Month To date Sell Thru %, I want to calculate
the value as Monthly Sales $/(Monthly Sales $ + Last week of the
month's Ending Inv $).  I want to use the LAST week's Ending Inventory
position to recalculate the sell through percent rather than adding
each week's Sell Thru.

Other mathematical roll ups (such as AVG, MIN, MAX, etc) will not work
either.  The only way to roll up this calculation it to re-calculate
after each pivot based on the source data.

Below is an example of the data.
Calculation at the weekly level
              Data
Style        Month    Week        Sell Thru %  Sales $    Ending Inventory $
62 - WHITE  DEC    12/2/2006        2.92            2,171    72,293
         12/9/2006        2.7            1,969    70,924
         12/16/2006    2.39            1,736    70,805
         12/23/2006    4.31            2,948    65,391
         12/30/2006    2.7            1,738    62,654
Below is an example of the Pivot table showing the Month To Date Sell
Thru as the SUM of the weekly sell thru %
         Data
Style        Month    Sell Thru %      Sales $    Ending Inventory $
62 - WHITE  DEC    15.02          10,562    342,067

The December Sell Thru % should be  14.44 rather than 15.02.
Total Monthly sales/ (Total Monthly Sales + Last week of month's
Ending Inv $)
(10,562/(10,562 + 62,654) ) * 100 = 14.44%

Any help on this issue would be GREATLY appreciated.  Since the data
is in a pivot table, we don't want to restrict the ways in which the
data can be viewed.  However, we are trying to balance this
flexibility with the data integrity. We are using Excel 2003.

Thanks-
Karen

Replies