Pivot Table - Calculated Field - Percentage - Rept

B

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top