PivotTable Calculated Field

  • Thread starter Thread starter Doug Fast
  • Start date Start date
D

Doug Fast

I am using a pivot table to calculate sales for the West. I have a
drop down which limits my data only to the west. When I try to add a
calulated field (Regular Sales + Bonus Sales), the pivot table lists
all the managers in the USA.

This is because all the items now have data. (A zero for my new
calcualated field). Even though Jane Doe does not work in the West
she has a 0 for total sales.

Is there a way to make the calculation give me a null value, so when I
don't select the "show items with no data" it hides all the folks I
don't want to see?


Doug
 
You could calculate the total in the data table, then add that field to
the pivot table.

Or, use code similar to the following, to hide items with zero total for
a calculated field (Excel 2002):

The following code will hide rows where the calculated item has a value
of zero:

'========================================
Sub HideZeroCalcItemRows()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Range
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi 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("Year") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each r In pt.DataBodyRange.Rows
On Error Resume Next
str = Cells(r.Row, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, pi.Value, pf2.Value, str)
If pd.Value = 0 Then
r.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
r.EntireRow.Hidden = False
End If
Next r
End Sub
'==================================
 
Back
Top