Hi Gilles,

Happy New Year to you and again many thanks for coming back on this. I
think I do understand more clearly what the difference is between the two
now, however I am still struggling to solve this problem.

To take your example, if a budget were set for say 25,000 for Amy and 23,000
for Bob per month how would you show this?

At a top level of sales by month I had no problem inserting a calculated
field for, in this example, 48,000 and this was duly shown in each month as I
required. However, I cannot get this to show the breakdown when Amy and Bob
are in the column headings unless I create a separate field for each budget,
i.e. Amy budget & Bob budget fields. Whilst this would work at a pivot table
level it becomes a nightmare to show in a graph as it the calculated field is
not just called 'budget'.

If you can provide any more help I would be truly grateful.

Regards,

Nigel


"Gilles Desjardins" wrote:

> Hi Nigel,
>
> Here is another way to look at it.
>
> Amy and Bob have worked all year from Jan to Dec and the results appear in a
> pivot table like so:
>
> Month Amy Bob
> Jan 23000 20000
> Feb 24000 23500
> Mar 24600 27000
> etc.
>
> You are asked to calculate their revenues per Quarter. That would be a
> calculated item: You add Jan+Feb+Mar and call it QTR1. To do this you click
> on the Pivot table tool bar menu, Formula, calculated item. Your cursor
> should be in the ROW area.
>
> Then you are asked to show what an increase of 5% would look like. You click
> in the COLUMN area and multiply the Sales by 1.05. This is a calculated
> field.
>
> Here is the "official" Excel explanation
> 1.. Decide whether you want a calculated field or a calculated item within
> a field.
> Use a calculated field when you want to use the data from another field in
> your formula. Use a calculated item when you want your formula to use data
> from one or more specific items (item: A subcategory of a field in
> PivotTable and PivotChart reports. For instance, the field "Month" could
> have items such as "January," "February," and so on.) within a field.
>
> 2.. Do one of the following. For best results in a PivotChart report work
> in the associated PivotTable report where you can see the individual data
> values that your formula calculates.
> Add a calculated field
>
> 1.. Click the report.
> 2.. On the PivotTable toolbar click PivotTable or PivotChart, point to
> Formulas, and then click Calculated Field.
> 3.. In the Name box, type a name for the field.
> 4.. In the Formula box, enter the formula for the field.
> To use the data from another field in the formula, click the field in
> the Fields box, and then click Insert Field. For example, to calculate a 15%
> commission on each value in the Sales field, you could enter = Sales * 15%.
>
> 5.. Click Add, and then click OK.
> Add a calculated item to a field
>
> 1.. If items in the field are grouped, right-click each group, point to
> Group and Outline on the shortcut menu and then click Ungroup.
> 2.. Click the field where you want to add the calculated item.
> 3.. On the PivotTable toolbar click PivotTable or PivotChart, point to
> Formulas, and then click Calculated Item.
> 4.. In the Name box, type a name for the calculated item.
> 5.. In the Formula box, enter the formula for the item.
> To use the data from an item in the formula, click the item in the Items
> list, and then click Insert Item (the item must be from the same field as
> the calculated item).
>
> 6.. Click Add, and then click OK.
> 7.. If you ungrouped items in step 1, regroup them if you want.
> 3.. For calculated items, you can enter different formulas cell by cell.
> How?
>
> For example, if a calculated item named Strawberries has a formula of
> =Oranges * .25 across all months, you can change the formula to =Oranges *.5
> for June, July, and August.
>
> 1.. Click a cell for which you want to change the formula.
> To change the formula for several cells, hold down CTRL and click the
> additional cells.
>
> 2.. In the formula bar type the changes to the formula.
> 4.. If you have multiple calculated items or formulas, adjust the order of
> calculation.
> How?
>
> 1.. Click the report.
> 2.. On the PivotTable toolbar click PivotTable or PivotChart, point to
> Formulas, and then click Solve Order.
> 3.. Click a formula, and then click Move Up or Move Down.
> 4.. Continue until the formulas are in the order that you want them to
> be calculated.
> Note When you add a formula to a PivotChart report or its associated
> PivotTable report, some chart formatting may be lost.
>
> N.B. Source data from OLAP databases doesn't allow you to create formulas.
>
>
>
> Hope this helps
>
>
>
> Gilles
>
>
>