RE: Custom Roll up and Drill down calculation for Excel Pivot table

Giganews Newsgroups
Subject: RE: Custom Roll up and Drill down calculation for Excel Pivot table
Posted by:  BobT
Date: Wed, 31 Jan 2007

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" wrote:

> 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

In response to

Custom Roll up and Drill down calculation for Excel Pivot table posted by Karen on 30 Jan 2007