Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated percentage field in a pivot table | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Field- Help | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Field- Help | Excel Worksheet Functions |