software

I have a pivot table set up reporting monthly margins based on detailed sales
data, on the back of which I have a pivot chart. I have successfully
inserted a summary budget number (as a calculated field) into the pivot table
and graph, and it looks fairly simple:

MonthMarginBudget
Jan58
Feb68
Etc.

I want to break this down to a product group looking at just margin and
budget, e.g.

AB
MonthMarginBudgetMarginBudget
Jan3424
Feb3434
Etc.

But I can’t get it to work – I know I will need to re-define the budget
numbers, but do I still need a calculated field or should I be using a
calculated item? Neither appears to be doing what I want.

Many thanks,

Nigel

A calculated item would be something like increase the sales of all dairy
products by 5%
A calculated item would be increase the butter by 5%, the yogurt by 6% and
the milk by 2%.
therefore you have to determine what is what in your database.

Does this make sense to you?

Gilles
quot;Nigel Drinkwaterquot; gt; wrote in
message ...
gt;I have a pivot table set up reporting monthly margins based on detailed
gt;sales
gt; data, on the back of which I have a pivot chart. I have successfully
gt; inserted a summary budget number (as a calculated field) into the pivot
gt; table
gt; and graph, and it looks fairly simple:
gt;
gt; Month Margin Budget
gt; Jan 5 8
gt; Feb 6 8
gt; Etc.
gt;
gt; I want to break this down to a product group looking at just margin and
gt; budget, e.g.
gt;
gt; A B
gt; Month Margin Budget Margin Budget
gt; Jan 3 4 2 4
gt; Feb 3 4 3 4
gt; Etc.
gt;
gt; But I can't get it to work - I know I will need to re-define the budget
gt; numbers, but do I still need a calculated field or should I be using a
gt; calculated item? Neither appears to be doing what I want.
gt;
gt; Many thanks,
gt;
gt; Nigel
Sorry, no. Did you mean to use the calculated item in both examples or is
one a calculated field?

I understand that I could use one of the fields to increase/decrease values
by a percentage, but can I not therefore use it to display a fixed value
dependant on category? I notice that the formatting was lost on the original
post, so to confirm I am keeping the rows the same with the months displayed
and trying to bring the product groups into the columns and use and IF
statement in the calculated item/field to give me the budget, e.g.:

IF(product group = a , 4 , IF(product group = b , 4 , etc.))...

If I can get this working then I can chart actual against budget at a lower
level then the top level that I currently have working.

Thanks,

Nigel

quot;Gilles Desjardinsquot; wrote:

gt; A calculated item would be something like increase the sales of all dairy
gt; products by 5%
gt; A calculated item would be increase the butter by 5%, the yogurt by 6% and
gt; the milk by 2%.
gt; therefore you have to determine what is what in your database.
gt;
gt; Does this make sense to you?
gt;
gt; Gilles
gt; quot;Nigel Drinkwaterquot; gt; wrote in
gt; message ...
gt; gt;I have a pivot table set up reporting monthly margins based on detailed
gt; gt;sales
gt; gt; data, on the back of which I have a pivot chart. I have successfully
gt; gt; inserted a summary budget number (as a calculated field) into the pivot
gt; gt; table
gt; gt; and graph, and it looks fairly simple:
gt; gt;
gt; gt; Month Margin Budget
gt; gt; Jan 5 8
gt; gt; Feb 6 8
gt; gt; Etc.
gt; gt;
gt; gt; I want to break this down to a product group looking at just margin and
gt; gt; budget, e.g.
gt; gt;
gt; gt; A B
gt; gt; Month Margin Budget Margin Budget
gt; gt; Jan 3 4 2 4
gt; gt; Feb 3 4 3 4
gt; gt; Etc.
gt; gt;
gt; gt; But I can't get it to work - I know I will need to re-define the budget
gt; gt; numbers, but do I still need a calculated field or should I be using a
gt; gt; calculated item? Neither appears to be doing what I want.
gt; gt;
gt; gt; Many thanks,
gt; gt;
gt; gt; Nigel
gt;
gt;
gt;

Sorry Nigel, I haven't forgotten you, just trying to formulate a better
response

Gilles
quot;Nigel Drinkwaterquot; gt; wrote in
message ...
gt;I have a pivot table set up reporting monthly margins based on detailed
gt;sales
gt; data, on the back of which I have a pivot chart. I have successfully
gt; inserted a summary budget number (as a calculated field) into the pivot
gt; table
gt; and graph, and it looks fairly simple:
gt;
gt; Month Margin Budget
gt; Jan 5 8
gt; Feb 6 8
gt; Etc.
gt;
gt; I want to break this down to a product group looking at just margin and
gt; budget, e.g.
gt;
gt; A B
gt; Month Margin Budget Margin Budget
gt; Jan 3 4 2 4
gt; Feb 3 4 3 4
gt; Etc.
gt;
gt; But I can't get it to work - I know I will need to re-define the budget
gt; numbers, but do I still need a calculated field or should I be using a
gt; calculated item? Neither appears to be doing what I want.
gt;
gt; Many thanks,
gt;
gt; Nigel
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 quot;officialquot; 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 quot;Monthquot; could
have items such as quot;January,quot; quot;February,quot; 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
GillesHi 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 amp; 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,

Nigelquot;Gilles Desjardinsquot; wrote:

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