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?

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
HIS


"fnov" wrote in message
...
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?

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

"fnov" wrote:

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?

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

fnov wrote:
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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote in message
...
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

fnov wrote:
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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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 wrote:
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" wrote in message
...

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

fnov wrote:

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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote in message
...
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 wrote:
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" wrote in message
...

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

fnov wrote:

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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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 wrote:
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" wrote in message
...

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 wrote:

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" wrote in message
...


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

fnov wrote:


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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote in message
...
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 wrote:
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" wrote in message
...

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 wrote:

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" wrote in message
...


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

fnov wrote:


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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

Jim May wrote:
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" wrote in message
...

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 wrote:

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" wrote in message
...


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 wrote:


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" wrote in message
...



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

fnov wrote:



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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html