Pivot Table Problem

[Rick]

I have a pivot table I created that basically counts items
so that I have am item name and count side by side after
the pivot table completes as below

Item1 24
Item2 15

Now my issue is that I want to count a secondary attribute
of these same items (say color for example) and express it
as a percent of the count already in the pivot table. I
have no problem getting the count but I can't seem to
figure out how to get the percentage part. I see options
to have the data as a % of the column or row, but not as a
percent of the existing count in the table. For example
the finished pivot table would be as follows assuming the
count of the secondary attribute was 6 for item1 and 5 for
item2:

Item1 24 25%
Item2 15 33%

Is there a way to do this in the pivot table or is this a
limitation ?

Many thanks in advance.

Rick

 

[Bernie Deitrick]

Rick,

Right click on your pivot table, the choose "Formulas", "Calculated Field...."

Then for the formula, use = Color / Item
(Use whatever your field names actually are, of course.)

Color and Item are the field names that appear in the lower box - you need to use the "insert field" button, so type =, then select
Color, hit insert field, type / , then select Item, and hit insert field.

And you're done....

HTH,
Bernie
Excel MVP

 

[Bernie Deitrick]

Rick,

Post a sample data table, maybe 10 lines total, and I'll take a look.

HTH,
Bernie
Excel MVP

 

[icestationzbra]

Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub


followed by---


Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="MySecretWord"
Next

End Sub


club these two macros together. they worked for me.

i got help from www.contextures.com - good site by debra for
pivottables.