Pivottable calculated item - how to suppress zero?

  • Thread starter Thread starter HMallen
  • Start date Start date
H

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.
 
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
 
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?
 
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.
 
Hi Shane,

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