Add result feilds to a pivot table
 

I am powering a pivot chart with a pivot table. The pivot table has a sum of
sales and a sum of profit for each month. Can I create a result feild in the
pivot table that divides the sum of profit by the sum of sales?

Of course I could do this outside the pivot table, but I want to do it in
the pivot table so the results are captured in the chart and are updated as
other variables like the pivot table page are changed on the chart. Not sure
if VBA can help or some other way?

Add result feilds to a pivot table
 

You can create a calculated field:
Select a cell in the pivot table
On the PivotTable toolbar, click PivotTable, then click, then choose
FormulasCalculated Field
Type a name for the field
In the formula box, enter a formula to divide profit by sales, e.g.:
=Profit/Sales
Click OK
In the pivot table, format the calculated field that was created.

Aaron wrote:
I am powering a pivot chart with a pivot table. The pivot table has a sum of
sales and a sum of profit for each month. Can I create a result feild in the
pivot table that divides the sum of profit by the sum of sales?

Of course I could do this outside the pivot table, but I want to do it in
the pivot table so the results are captured in the chart and are updated as
other variables like the pivot table page are changed on the chart. Not sure
if VBA can help or some other way?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Add result feilds to a pivot table
 

Perfect! I named the field "margin" but it shows up as "Sum of Margin". It
really is not the sum of margins. How do I get rid of the "Sum of"?

"Debra Dalgleish" wrote:

You can create a calculated field:
Select a cell in the pivot table
On the PivotTable toolbar, click PivotTable, then click, then choose
FormulasCalculated Field
Type a name for the field
In the formula box, enter a formula to divide profit by sales, e.g.:
=Profit/Sales
Click OK
In the pivot table, format the calculated field that was created.

Aaron wrote:
I am powering a pivot chart with a pivot table. The pivot table has a sum of
sales and a sum of profit for each month. Can I create a result feild in the
pivot table that divides the sum of profit by the sum of sales?

Of course I could do this outside the pivot table, but I want to do it in
the pivot table so the results are captured in the chart and are updated as
other variables like the pivot table page are changed on the chart. Not sure
if VBA can help or some other way?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Add result feilds to a pivot table
 

Click on the cell that says Sum of Margin.
Type Margin<space, and then press Enter
You can't use a heading that's exactly the same as the field name
(Margin), so adding the space character makes it look like Margin, but
slightly different.


Aaron wrote:
Perfect! I named the field "margin" but it shows up as "Sum of Margin". It
really is not the sum of margins. How do I get rid of the "Sum of"?

"Debra Dalgleish" wrote:


You can create a calculated field:
Select a cell in the pivot table
On the PivotTable toolbar, click PivotTable, then click, then choose
FormulasCalculated Field
Type a name for the field
In the formula box, enter a formula to divide profit by sales, e.g.:
=Profit/Sales
Click OK
In the pivot table, format the calculated field that was created.

Aaron wrote:

I am powering a pivot chart with a pivot table. The pivot table has a sum of
sales and a sum of profit for each month. Can I create a result feild in the
pivot table that divides the sum of profit by the sum of sales?

Of course I could do this outside the pivot table, but I want to do it in
the pivot table so the results are captured in the chart and are updated as
other variables like the pivot table page are changed on the chart. Not sure
if VBA can help or some other way?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html