Custom Roll up and Drill down calculation for Excel Pivot table

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

0
ksaboura (2)
1/30/2007 7:56:50 PM
excel 39879 articles. 2 followers. Follow

2 Replies
451 Views

Similar Articles

[PageSpeed] 13

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
> 
> 
0
Bobt (10)
1/31/2007 2:42:00 PM
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


0
ksaboura (2)
1/31/2007 4:24:04 PM
Reply:

Similar Artilces:

customizing quote merge template
I have 2 major problems that I just cannot seem to be able to resolve. When using the mail merge template to do a quote it seems that there are only a very limited range of "fields" that are made available to the user. These DO NOT include the actual accounts "Address" only shipping and billing address - 99% of our clients don't use these address's so all our data is in "Address_line1" etc Neither do the fields for "product description" appear in the list only the products name. All our quotes have always included a short four or five line desc...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

Customizing AdvisorFYI
I have an account for some stock options in a privately-owned company. Approximately monthly, AdvisorFYI keeps telling me, "It's time to update your investment prices in Money." There isn't a price update that often. Is there a way to turn this message off without turning of the other useful AdvisorFYI Investment warnings? ...

Unable to click customizing outlook today button
Unbable to click the customising outlook today button for Outlook 2000 install in the windows xp profession machine. I have updated the office 200 patch 3. But it seem remain the same problem. http://support.microsoft.com/default.aspx?scid=kb;en-us;820575 might be helpful. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Clarence" <anonymous@discussions.microsoft.com> wrote in message news:06d101c3c939$78175620$a401280a@phx.gbl... > Unbable to click the customising outlook today button for > Outlook 200...

SpreadSheetML (XML for Excel)
Hello All, A client of mine has Office X for Mac. I am wondering if this edition of Excel supports SpreadSheetML. I have heard that it also support the new Open XML formats (same as Office 11). Does anyone know if either of these statements is accurate? Thanks in advance, -KJ Hi KJ: I believe that both statements are wrong. The XML converter for Office Next on the Mac has not yet been developed. It's not likely to come until after the PC Office team ships their product to retail -- until they stop CHANGING the thing :-) My guess is that the converter won't appear until sometime...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Input to custom reports
Hello, I'd like to be able to input values to a custom report like the Year. Is there any way to package a custom report and provide input to it? Every year the user has to go into the custom report and change the Year restriction. Alternatively, (this is a payroll report), is there any global variable or some other back door that I can use to get the current year in a custom report? Given the report writer may not be the ticket what would the recommendation be to create custom reports that require input? VB.NET? -- Russ Using VBA you can allow for input in the custom reports. B...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Unable to sort on customer CRM 4.0
Hi When you try to change the campaign response views to have customer as sorting CRM gives an error. You can't even click on customer column to sort when it's now customer that is default sorting. Is this a bug ? same here. I think its a bug "Help needed" wrote: > Hi > > When you try to change the campaign response views to have customer as > sorting CRM gives an error. You can't even click on customer column to sort > when it's now customer that is default sorting. > > Is this a bug ? > > > > ...

View original Excel after saving over it
In error, i hit save instead of save as. I'd like to view the original documents contents. is this at all possible. i use windows xp. excel 2003 Not very likely. Try a Google search action if you haven't already, but I think it's impossible. This underscores the importance of backups. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "houb" wrote: > In error, i hit save instead of save as. I'd like to view the original > documents contents. is this at all possible. i use windows xp. excel 2003 In article <C2A3F0E7-24E0-43A5-809A-ECA719...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

ExCel programming
I am working on a spreadsheet for work where it will calculate commissions to be earned based on my sales. There is a table that I have to use to determine what the percentage would be, how would I write an equation for that. For example, the equation would have to say: if say D3 was = or < than %, use cell# whatever and then have a sum in there. The table is on another sheet in the document so that would have to be there too. Basically, the way that my commissions are calculated is that we have to take what I sold in any given month and find out what percentage of my yearly plan i...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Calculating totals
I have two columns on a worksheet: "Project Codes" and "Totals." I would like to have columns on another worksheet that will automatically total up the different project numbers "A,B,C,etc." How do I do that? Thanks in advance for your help, Technically Handicapped Enter a *unique* list of your "Project Codes", starting in A2 of Sheet2. In B2, enter this formula: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) And copy down as needed. -- HTH, RD ===================================================== Please keep all correspondence within the G...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...