Calculated Field on PT data shown as '% of Column'

[Alon Gotesman]

I posted this question at mrexcel.com but got no responses. Hopefully
someone here can point me in the right direction!

So here is what some data in my Pivot Table looks like:

Code:
AGENT JUNE JULY
Jeff 10 8
Mark 5 12
Drew 15 4


I wanted my data to be displayed as a percentage of the column, so I
change the Pivot Table field setting (by going to 'Field Settings ->
Options -> Show data as: % of Column' and have something like:

Code:
AGENT JUNE JULY
Jeff .33 .33
Mark .16 .50
Drew .50 .16


Now suppose that I want a new column that divides June by July. What
I'm getting is that Excel is dividing the original values, not the
percentages that are actually displayed. So instead of Jeff having a
ratio of 1 (.33/.33), he has 1.25 (10/8). Here is what I'd *like* it to
look like:

Code:
AGENT JUNE JULY RATIO
Jeff .33 .33 1.0
Mark .16 .50 .32
Drew .50 .16 3.1




Is there a way to get the calculated field to use the values displayed
in the Pivot Table?

Thanks!

 

[Herbert Seidenberg]

Start out with
Agent JUNE JULY
Drew 15.00 4.00
Jeff 10.00 8.00
Mark 5.00 12.00

Then insert 3 calculated items in the column field
with the names JunePCT, JulyPCT and Ratio with these formulas
=JUNE/((Drew JUNE)+ (Jeff JUNE)+(Mark JUNE))
=JULY/((Drew JULY)+(Jeff JULY)+(Mark JULY))
=JunePCT/JulyPCT
If you then hide JUNE and JULY fields, the PT will look like this:

Agent JunePCT JulyPCT Ratio
Drew 0.50 0.17 3.00
Jeff 0.33 0.33 1.00
Mark 0.17 0.50 0.33

 

[Debra Dalgleish]

No, the calculated field will use the sum of the underlying values. You
could do the calculation outside of the pivot table, referencing the
cells, or using the GETPIVOTDATA function to extract the data.

 

[Alon Gotesman]

That's not a bad idea, except for two problems:

1) Each month is actually a separate column field, since the raw data
I'm receiving is structured in exactly the same way my Pivot Table
looks: with a column for agent name and a column for each month. So I
cannot use a Calculated Item, since the calculation is on two different
fields (that's why I'm using Calculated Field).

2) In reality, there are hundreds of agents, and the names of these
agents change regularly. So it's not viable to create a solution that
refer to specific item names -- a more generalized method to solving
this problem is needed.


I'm starting to think that Debra is correct, and that there really is
no way to get the Pivot Table to not use the underlying data in a
custom calculation. I guess I'll create my calculations outside of my
Pivot Table, even though it's not nearly as good for many reasons
(formulas will need to be added/removed if PT source changes and number
of rows changes, loss of consistency in formating, etc).


It's always disappointing when I find some annoying limitation in
Excel, especially when it's something as seemingly nonsensical as this.

 

[Debra Dalgleish]

Perhaps you could add fields to the source data, and calculate the
percent of column there, for each row. In the pivot table, sum these
fields in the data area, and calculate the differences.