Pivot table row headings are repeated with calculated item

E

Eric D

Why do extraneous rows show up in pivot table with
calculated item?

Scenario:
I create a pivot table with 2 row headings (location,
department) then add 1 column (month) and 1 data
(headcount). Then I create a "calculated item" (gain) on
the column (month) of "=February-January".

When it adds it to the pivot table I get extraneous
calculations for "departments" that don't exist in
certain "locations". It returns a zero. What I need is
for those "departments" not to appear at all.

Anyone recognize this? Any solutions?
Thanks
Eric

Example
City Department Month Headcount
Dallas Sales January 10
Dallas Marketing January 20
Dallas Sales February 15
Dallas Marketing February 25
San Diego IT January 10
San Diego HR January 20
San Diego IT February 15
San Diego HR February 25


Pivot Table
Sum of Headcount Month
City Department January February
Gain
Dallas HR 0
IT 0
Marketing 20 25 5
Sales 10 15 5
Dallas Total 30 40 10

San Diego HR 20 25 5
IT 10 15 5
Marketing 0
Sales 0
San Diego Total 30 40 10

Grand Total 60 80 20
 
P

Peter Horncastle

Dont know if this is what your problem is or a solution but I used to
find Pivot tables annoying by adding extra total cols that seemed to
repeat data already present.

You can fix this by right clicking on the field heading click field
settings and set subtotals to NONE

Maybe this might help!?

Pete
 
D

Debra Dalgleish

You could use code to hide the calculated items that are zero. For example:

'=====================================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub

'===================================
 

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