Pivottable calculated item - how to suppress zero?

[HMallen]

I have created a basic pivottable in Office 2007. It has three row labels
and a sum of the value of each of the row labels. I then created a
calculated item that sums a few of the "third row label" values. After I
have done so, I get this calculated item throughout the report even where
there were no values to sum from the "second row label". So the report
becomes huge with many many rows showing a zero value for the calculated
item. I am not sure if this is clear.

Ultimately, I am wondering if there is a way to suppress all rows for this
calculated item that have zeros or is there a better way to prevent this from
occurring.

Sample BEFORE the calculated item is created:

XXX 490
XXXXXXXXXXXX 490
Blue Shield Claims 26
Commercial Claims 113
Medicaid Claims 11
Medicare Claims 272
Paper Claims Processing - Level 1 68

YYY 16016
YYYYYYYYYYYYYYYYYY 16016
Commercial Claims 15839
Medicare Claims 177

Sample AFTER the calculated item is created:

XXX 490
AAAAAAAAAAAAAAAA 0
Gov't 0
BBBBBBBBBBBBBBBBBB 0
Gov't 0
XXXXXXXXXXXXXXX 490
Commercial Claims 113
Paper Claims Processing - Level 1 68
Gov't 309
CCCCCCCCCCCCCCCC 0
Gov't 0
DDDDDDDDDDDDDDDD 0
Gov't 0
EEEEEEEEEEEEEEEEEE 0
Gov't 0

and so on.

 

[ShaneDevenshire]

Hi,

There are many options that may apply, but I'm not clear on exactly what you
calculated formula does and where it appears.

If you can you can send a copy to me

 

[HMallen]

Unfortunately I cannot send you the data right now but to simplify my
question....

I can use a value filter to eliminate all rows (calculated or not) in the
pivot that have zero value. But when I use the value filter for this
purpose, it removes the label filter that I had been using on the same field.
I need both filters to be in place on this field at the same time.

Do you know if there is a way to use a value filter to eliminate rows from a
field with zero value and also a label filter to remove specific items in the
same field at the same time?

 

[Shane Devenshire]

Hi,

Well without seeing your pt, my first suggestion is to change to Tabular
layout - Choose PivotTable Tools, Design, Report Layout, Show in Tabular
Form. See if that helps.

 

[HMallen]

Hi Shane,

I tried it but it didn't work. I will email you a sample spreadsheet.
Thank you for trying to assist me.