Custom Roll up and Drill down calculation for Excel Pivot table

[Karen]

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

 

[Guest]

Do you have access to either MS-SQL Server Advanced or Hyperion Essbase
(a.k.a. Hyperion Analytical Services)? Both of these can create
multi-dimensional databases - or cubes - which natively report in Excel as
pivot tables. Unlike the Excel based pivot tables (where you have to have
the data within the spreadsheet), these two options store the data internally
and then aggregate and recalculate at any level the key performance
indicators (KPIs) that you're looking for. Sadly, using the native Pivot
tables of Microsoft (and Lotus, Paradox, etc.) any calculated member you
create will only be correct at the level and intesection you define while a
true cube will recalculate the value at any level across any and all
dimensions and selections. These cubes are at the heart of a Business
Intelligence (BI) solution. Unfortunately, neither tool is overly user
friendly for building cubes (read: some assembly required). But if you have
an IT dept with either solution, they should be able to whip up the solution
quickly and give you the added benefit of access more data, direct from
source, faster than you're doing it now (and both are SOX compliant).

 

[Karen]

Thank you for your response and help. We do currently have various
datasets available in multi-dimensional databases and have (like you
said) used the cube functionality to calculate this type of
information. However, we were trying to avoid creating a cube to
house this small subset of data. We were hoping that a simple and
dynamic pivot table would do the trick since it takes us less
development and maintenance time to create a pivot table over the data
than a cube over the data. I wasn't too confident it was possible in
a pivot table, but I thought I would try. Thank you for your help. We
will have to revisit the idea of putting the data in a cube.

Thanks-
Karen