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! |
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! |
All times are GMT +1. The time now is 08:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com