Pivot Table - Calculated Field - Percentage - Rept
 

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

Pivot Table - Calculated Field - Percentage - Rept
 

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.


Blacken wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Pivot Table - Calculated Field - Percentage - Rept
 

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