Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Using Calculated Field or Item in Pivot Table
Hi there - am having difficulty figuring out the pivot table Calculated
Field and Calculated Item features in Excel 2003. My pivot table is setup as follows: Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Total * Data is sum of revenue For this example: Month1, Month2 and Month3 is considered "BASE" Month4, Month5 and Month6 is considered "CURRENT" (both 'base' and 'current' are variables, so they will have to be re-defined each month) What I want to do: Is create a column which calculates "INCREMENTAL" Incremental = Current - Base In the end, what i would like to have is a pivot that looks similar to this: Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Incremental or -------------BASE------------ ----------CURRENT-------- Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Incremental I have tried using grouping to group the Base months, and the Current months. For the life of me, i can't figure out how to do this. Would appreciate your kind assistance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Using Calculated Field or Item in Pivot Table
Hi there,
Insert a Calculated Item named "Base" In your "fields" I guess you have "Month" with Items: Jan-Feb-Mar-.... Type in the Formula field: = Jan+Feb+Mar Create similarly for "Current" with months: Apr-May-Jun Create a 3rd Calculated Item called "Incremental" with Formula: Current-Base Go back to your PivotTable and uncheck in the "Month"-field the "Base" & "Current" item if you do not wnat to see them ... You explained the methodology yourself ...try it with "calculated Items", it works. I don't think it is feasible with Groupings though HTH Jen "sg" wrote in message ... Hi there - am having difficulty figuring out the pivot table Calculated Field and Calculated Item features in Excel 2003. My pivot table is setup as follows: Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Total * Data is sum of revenue For this example: Month1, Month2 and Month3 is considered "BASE" Month4, Month5 and Month6 is considered "CURRENT" (both 'base' and 'current' are variables, so they will have to be re-defined each month) What I want to do: Is create a column which calculates "INCREMENTAL" Incremental = Current - Base In the end, what i would like to have is a pivot that looks similar to this: Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Incremental or -------------BASE------------ ----------CURRENT-------- Name - Item - Month1 - Month2 - Month3 - Month4 - Month5 - Month6 - Incremental I have tried using grouping to group the Base months, and the Current months. For the life of me, i can't figure out how to do this. Would appreciate your kind assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Calculated Field/Item in a Pivot | Excel Worksheet Functions | |||
Pivot Table Calculated field item | Excel Discussion (Misc queries) |