MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot table and Calculated item field

Posted by Jon H on January 15, 2002 8:02 AM

I have a large pivot table that has a calculated item field (in my case, the calc item is col.1-col.2).

When I use the calculated item field, it lists every calculated item, regardless of whether there is actually data in the row it is calucalting. So I end up with the pivot table listing a lot of zero info, and it makes for a table a lot longer than it needs to be.

Is there a way the extra lines with zero in the calc item field ca be made to be hidden?


Posted by Mark W. on January 15, 2002 8:56 AM

I'm struggling to understand your problem. As I
understand it you've added a column to your
PivotTable. This new column is a Calculated Item
based on the difference of the 1st and 2nd Pivot-
Table columns. Somehow the addition of this
new column has made your PivotTable "alot longer".
I don't see how this can happen since the new
Calculated Item will only produce a value for
existing rows. Please explain further or provide
a simple example that illustrates your problem.

Posted by Jon H on January 15, 2002 10:05 AM

You would think the pivot table would only produce values for existing rows, but it calculates a value for every line in the pivot table, regardless of whether there is data in the line item. For example, say the pivot table is calcing the following data:

Acct Num Actual Budget
61100- 10 5
61200- 0 0

The result of a pivot table would only list the first row (the second would not show, as there is no actual data to summarize on the report). When I add a calculated field (budget-actual), ALL rows show up on the table, even those that start out as not showing. It looks like the calc item now has a "0" value (becuase 0-0=0). And that is causing all account numbers to show, even when there is no value in them.

Thanks for your response.

Posted by Mark W. on January 15, 2002 11:28 AM

I still cannot reproduce your configuration. Is
'Acct Num' 61200 included on a row in your data
list or database? By chance have you checked the
"Show items with no data" option for the 'Acct Num'