Charting getpivotdata formula

[wooddsw]

I have a pivot table that resembles:

Calls Targets

January 100 25
February 50 10
March 25 5
etc

I need to calculate a ratio from this pivot table which I can do in the cell adjacent to the Targets value and copy down all rows. The formula is basically (Targets/Call)*100 to get the percentage penetration.

Calls Targets Penetration

January 100 25 25%
February 50 10 20%
March 25 5 20%
etc

Now I'd like to get that percentage into the linked PivotChart. Any suggestion on how to accomplish this would be greatly appreciated.

Window XP SP2
Excel 2007
Data Import via a SQL data connector.

 

[GlennUK]

Have a calculated field in the pivottable instead of calculating the percentages outside. PivotTables/Formula/Calculated Field, name of Penetration, with a formula of Targets / Calls. Change the number format to percent afterwards.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wooddsw]

I understand I can create a calculated field but I need to reference the summary cells within the PivotTable and they aren't available in the Fields listbox of data source columns to choose from.

When I try and paste the following formula into the Insert Calculated Field and add it i get "References, names, and arrays are not supported in PivotTable formulas.

=(GETPIVOTDATA("Targets",Sheet1!$A$4,"MonthName",Sheet1!A6)/GETPIVOTDATA("Total Calls",Sheet1!$A$4,"MonthName",Sheet1!A6))*100

This formula properly creates the penetration calculation I want and I can replicate it outside the pivot table on the appropriate rows but I need to be able to graph it as an additional point on the PivotChart.

 

[GlennUK]

... I need to reference the summary cells within the PivotTable and they aren't available in the Fields listbox of data source columns to choose from.

Show the layout of the source data, and the layout of the pivottable, and what the fields in the pivottable are, and which of those fields you want to use in the calculation.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wooddsw]

Just discovered my own solution....

When creating the Calculated Field, I just need to use the 2 fields from the data source, in this case Target and Distinct Call fields, in their own formula.

ie. =Target/DistinctCall

When these 2 fields are using in the aggregate calculation the summary of the 2 fields calculate normally and my formula then calculates on the aggregate results. This is then incorporated into the PivotChart and maps just fine.

Thanks guys for the input.