Pivot Table - Calculated Field

[Guest]

Would appreciate assistance with Calculated Fields in a Pivot table.

I would like to perform a calculation which invovles a reference to the
total of a field generated in a pivot table.

I would like to calculate the average total sales for a sales team, by
dividing each sales persons total sales, by the teams total sales.

I have tried using the average function, but this only calculates each
individuals average for each sales item.

I cannot work out how to refer to totals generated by pivot tables in a
formula.

Can anyone assist?

 

[Nick Hodge]

In a lot of cases you will not need a calculated field. If you are getting
the 'Sum' of sales for each salesperson/team then simply adding the same
field again but setting it as 'Average' should suffice.

To do this invoke the wizard and select the 'layout' button. Add the sales
field a second time. (Excel will default to Sum), double click the added
sales field and change it to 'Average'

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Hello Nick,

Thanks for your prompt response.

I have tried adding the sales field again, and changing the calculation to
average. This returns the average sales value per sale, rather that total
sales.

What I am trying to calculate is to divide each individuals sales by the
total team sales. This would then enable me to calculate how each sales
person is performing within each team, based on the average sales within each
team.
Thanks

 

[Debra Dalgleish]

To see the percent of each person's total in their team's total, you can
do the following:

In the Pivot table, put salesperson in the row area, and sales team in
the column area.
Put two copies of the Sales field into the data area.
Right-click the heading for one of the Sales fields
Choose Field Settings
From the dropdown list for 'Show data as", select '% of column'
Click OK

 

[Jim May]

Using Excel 2002 here,,
When initially creating a PT, the Graphic "DROP-DATA" Box appears.
Once you create and it convert to an actual PT, how can you "get - it -
back",
that is view it on-screen (with its current setting)?
Also, after creating the % of column (requested by OP) My Data field button
name changes to Data (I guess the default if there are multiple fields in
the Data
Area?)..
Anyway If I click the Down-arrow of the Data button and select the newly
renamed
"% of Sales-Team", only the %'s show << The $$$..?? Have they been "Hid" ?
I don't see a way of getting them back, without going back to the
layout-screen.
Am I missing something here?
TIA,
Jim

PS: Can Calculated field be used in Page section?, Row section and /or
Column
Section, or are they limited to only the Data Section?

 

[Debra Dalgleish]

If you select a cell in the pivot table, you can see the blue outline of
the layout areas. You can drag fields from the field list to these areas.

If you remove a field from the data area, it's gone from the pivot
table. To get it back, you would have to drag it from the field list,
then adjust the field settings.

Calculated fields are limited to the data area.

 

[Jim May]

Thanks Debra for the answers, I'm inching forward with your help.
Jim

Are Calculated Items restricted also (like Calculated Fields) to the Data
Area?

 

[Debra Dalgleish]

You're welcome. Yes, calculated items are also restricted to the data
area. I'm not sure what you're trying to do, but maybe you could add
some columns to your source data, and do the calculations there, then
add those new fields to the pivot table.

 

[Jim May]

I'm not actually wrapped you in a given example, it's just that I'm
reading through Paul Cornell's new book A Complete Guide to
Pivot Tables...
Calculated fields and Items only cover 3 or 4 pages (rather brief,
if I say so).

 

[Debra Dalgleish]

The book covers the basics nicely, but has very little information on
intermediate to advanced topics.