Pivot Table - How to Create Calculated Items 1

[chouna]

In my external database, I have a table with this structure:

Employee Dept Salary

Under the Dept, I will, of course, have different departments like Finance, Logistics, Sales, Marketing, Production, etc. Here are the questions:

1. How do I create a calculated field based on the department? E.g. I would like to make a budget for Marketing which is 10% of Sales+Marketing salaries. Logistics budget will be Production+Logistics salaries + 15%. I don’t want to use the IF formula as my experience tells me that Pivot formulae do not calculate on individual records but aggregates.

2. How do I apply Calculated Items? I have gone through the Help file but find the examples very inadequate. In my case, my calculation would be dependent on other items within the field, not the same item.

Thanks in advance.

 

[lupins46]

In general you should do your calculations in the query that you use as the source for the pivot table.

 

[BlueHorizon]

Hi,
I use Excel 2003 - don't know which version you're using so this might not be exactly what you see but maybe this helps:
Select the PivotTable data field "brick" in your pivot table that represents the field to which you want to add the calculation. Click on "Pivot Table" on the PivotTable toolbar. Select Formula on drop-down menu. Select Calculated Field and enter your formula.
HTH,

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[chouna]

Thanks for your replies. I thought that "Calculated Items" may just be the solution to my woes. I may be expecting too much from Excel but I'm not losing hope. We might just have to wait for pivot tables to mature in Excel (it has gone a long way since Office97). I remember a time when you could not create a pivot without a Column (a simple aggregated summary of all numeric fields) and now we even have dynamic color formattings.