Pivot Table Calculated Item-Rev by Qty

Hello All,

I have a pivot table for which I created a caluclated item
to devide Rev by Qty (which are both possible entries in
the field Category). Because Excel is now performing this
calculation even on combinations that don't exist it is
showing each line rather than just those for which data
exists. So where I had

Division Product Rev Qty
North Product A 3000 10
Product C 5000 100
South Product A 5000 15
Product B 4000 25

I now have

Division Product Rev
North Product A 300
Product B #DIV/0!
Product C 50
South Product A 333
Product B 160
Prodcut C #DIV/0!

I know how to change the #DIV/0! symbols to blanks or
zeros, but is there a way to get the rows to disappear
completely? Is it possible to perhaps filter for items >
0? Any suggestions would be appreciated. Thanks.

Erin

Try a calculated field rather than a calculated item.

"Erin Searfoss" wrote:

> Hello All,
>
> I have a pivot table for which I created a caluclated item
> to devide Rev by Qty (which are both possible entries in
> the field Category). Because Excel is now performing this
> calculation even on combinations that don't exist it is
> showing each line rather than just those for which data
> exists. So where I had
>
> Division Product Rev Qty
> North Product A 3000 10
> Product C 5000 100
> South Product A 5000 15
> Product B 4000 25
>
> I now have
>
> Division Product Rev
> North Product A 300
> Product B #DIV/0!
> Product C 50
> South Product A 333
> Product B 160
> Prodcut C #DIV/0!
>
> I know how to change the #DIV/0! symbols to blanks or
> zeros, but is there a way to get the rows to disappear
> completely? Is it possible to perhaps filter for items >
> 0? Any suggestions would be appreciated. Thanks.
>
> Erin
>

Instead of doing the calculation, you could add a column to your pivot
source data, and calculate there. Then, add the new field to the pivot
table, and it should only appear in the relevant rows.

Erin Searfoss wrote:
> Hello All,
>
> I have a pivot table for which I created a caluclated item
> to devide Rev by Qty (which are both possible entries in
> the field Category). Because Excel is now performing this
> calculation even on combinations that don't exist it is
> showing each line rather than just those for which data
> exists. So where I had
>
> Division Product Rev Qty
> North Product A 3000 10
> Product C 5000 100
> South Product A 5000 15
> Product B 4000 25
>
> I now have
>
> Division Product Rev
> North Product A 300
> Product B #DIV/0!
> Product C 50
> South Product A 333
> Product B 160
> Prodcut C #DIV/0!
>
> I know how to change the #DIV/0! symbols to blanks or
> zeros, but is there a way to get the rows to disappear
> completely? Is it possible to perhaps filter for items >
> 0? Any suggestions would be appreciated. Thanks.
>
> Erin


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