Pivot Table - Calculated Field - Percentage - Rept

[Blacken]

Morning,

I am trying to set up a calcualted field in my pivot table (Excel 2007)

What I need is a way to get a total for a column

For illustration

Type Volume
a 200
b 100
c 50
c 50

The toal in the Volume column would be 400

I will then be using the total by dividing the type by the total in a
calculated field to get a percentage

a 50%
b 25%
c 12.5%
d 12.5%

I am aware that there is a percentage capability already buit into the pivot
table, but what I will be doing is using my calculated field in a rept
function to build those little in cell bar graphs, within the pivot table

Something along the lines of =rept("|",Volume/Total_Volume*200)

||||||||||
|||||
|
|

Question is the methodology for the "Total_Volume" part. Had thought it
might involve a form of the getpivotdata function? Or is there a cleaner way?

Or is there a way to use that inbuilt % of total capability within a rept
function?

An alternative is to use the conditional shading relased in 2007 but I
prefer format from the rept function, and some users that may use my
spreadsheet may not always have 2007

Many thanks in advance

 

[Debra Dalgleish]

You can't use the total sum of a field in a calculated field, so you
won't be able to refer to the total volume.

Perhaps you could do something outside the pivot table, and use
GetPivotData to pull the values.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Custom bar graph.
With GetPivotData.
Dynamic auxiliary range.
http://www.mediafire.com/file/mtmkjneojwt/08_10_09.xlsm