Pivot calculated field

  • Thread starter Thread starter Hillrg
  • Start date Start date
H

Hillrg

I want to include the right hand column inside the pivot table. It wa
calculated externally for this example. The formula is Plan Pct
Actual Total.

[image: http://www.pbase.com/image/25606965/original.jpg]

The formula for the green bordered cell is 49.64% * 3,132,547.

I cannot get percent of total in a calculated field, nor can I use th
column total in a calculated field. Is this possible?

BTW, I cannot get the image to appear inside this note - please clic
to see - thanks.

TI
 
I wasn’t able to find away either.

What I did was to do the calculations (in my case) AB, make that a
hidden column. Then use in AC use “=SUM(SUMIF(AB10,">0",AB10))” and
then conditional format that AC, so that if a cell equals 0 then forma
white (blend with back ground to hid it).

Copy the AB and AC formulas down as far as you thing the pivot tabl
will go. It gives the illusion that it’s part of the pivot table
 
You can add two columns to the data table, and calculate the amounts
there, then add the new fields to the pivot table. For example, create a
new column named PlanPct. Enter the following formula in row 2 of the
new PlanPct column:

=SUMIF($B$2:$B$36,B2,$C$2:$C$36)/SUM($C$2:$C$36)/COUNTIF($B$2:$B$36,B2)

In this example, Species is in column B, and Plan is in column C.

Add another column, named PlanProrate. Enter the following formula in
row 2 of the PlanProrate column:

=SUM($D$2:$D$36)*E2

Actual is in column D and PlanPct is in column E.

Copy the formulas down to the last row of data.

Add the new fields to the pivot table, summarizing by Sum.
I want to include the right hand column inside the pivot table. It was
calculated externally for this example. The formula is Plan Pct *
Actual Total.

[image: http://www.pbase.com/image/25606965/original.jpg]

The formula for the green bordered cell is 49.64% * 3,132,547.

I cannot get percent of total in a calculated field, nor can I use the
column total in a calculated field. Is this possible?

BTW, I cannot get the image to appear inside this note - please click
to see - thanks.

TIA
Click to expand...
 
Back
Top