Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Calculated Item
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Item wise balance in Pivot Table | Excel Discussion (Misc queries) | |||
Item wise balance in Pivot Table | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table | Excel Worksheet Functions | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |