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
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 %
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.