Calculated Items in Pivot Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a calculated item in a pivot table. I have a field, Acct Type, which contains Rev & Exp as field items. I created a calculated item to subtract Exp from Rev to calculate a Cont Margin.
This works great, as long as I only have a single Row item.
I want to calculate this field and display the results by Center.
The table is setup to show center as a Row and Acct Type as a column, with either a Act or Bud $ value as my data.
When I choose Center as the row, the table looks like this:
Center Rev Exp CM
Center1 1000 500 500
Center2 1000 500 500
Center3 1000 500 500

This is perfect, however, if I try to add a second row - such as center description, the table shows every combination of Center and Center description, even if they are not valid - as follows:

Center Desc Rev Exp CM
Center1 Desc1 1000 500 500
Desc2
Desc3
Center2 Desc1
Desc2 1000 500 500
Desc3
Center2 Desc1
Desc2
Desc3 1000 500 500


Is there any way to add the second row without having all of the invalid cell combinations show up? Any help or direction would be greatly appreciated!!!

Thanks
Chris
 
AFAIK, you can't change this behaviour. You could use a macro (like the
following one) to hide the zero rows, and run it when you refresh the
pivot table:

'-------------------------------
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rRow) = 0 Then
rRow.EntireRow.Hidden = True
Else
'DD --I added this to unhide
'any previously hidden rows
rRow.EntireRow.Hidden = False
End If
Next rRow
End Sub
'-------------------------------
 
Back
Top