Pivot Table - Calc Item Problem
 

Hello,

I'm trying to insert a quarterly subtotal (Jan+Feb+Mar) in my pivot table.
The problem I'm having is that when I insert the calculated item, my pivot
table creates a bunch of blank rows when I only want subtotals for fields
that have data.

Example:
Company Region Jan Sales Feb Sales Mar Sales
MSFT South 10 10 10
HPQ North 15 15 15

Here's the output I'm trying to get with "Q1" my calc item:
Company Region Jan Sales Feb Sales Mar Sales Q1
MSFT South 10 10 10 30
HPQ North 15 15 15 45

Here's the output I'm getting with unwanted zero/blank cells:
Company Region Jan Sales Feb Sales Mar Sales Q1
MSFT South 10 10 10 30
North 0 0 0 0
HPQ South 0 0 0 0
North 15 15 15 45

How do I only get quarterly subtotals for rows that have data? Another way
to put it, how do I get rid of all the rows with zeros?

Thanks in advance.

I've got the same problem as Scott. Lots of rows with unwanted zeros
when calculated item inserted. Has anyone found a way of getting rid of
them?

alison


--
alisonb
------------------------------------------------------------------------
alisonb's Profile: http://www.excelforum.com/member.php...o&userid=23923
View this thread: http://www.excelforum.com/showthread...hreadid=350249

You can use programming to hide the rows with a zero total. For example:

'======================================
Sub HidePivotZeroRows()
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
End If
Next rng
End Sub

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

Sub UnhidePivotRows()
'unhide all rows
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
rng.EntireRow.Hidden = False
Next rng
End Sub
'====================================


alisonb wrote:
I've got the same problem as Scott. Lots of rows with unwanted zeros
when calculated item inserted. Has anyone found a way of getting rid of
them?

alison




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html