pivot table problem

[JonH@JWCE]

I am using a calculated item in a pivot table.

on pivot tables without calculated items, the pivot table results only show summary info when there are actual results in the source data.

When I use the same data and add the calculated item, the pivot table gives a line item for all combinations of data, even when there isn't any data to summarize.

Does anyone know how to get around this problem with calculated items in pivot tables?

 

[Mark W.]

Can you describe your Calculated Item formula?

 

[JonH@JWCE]

Yes - it is a pivot table that compares actual and budget amounts, the calculated item is the variance between actual and budget (the difference between the two). it is calculated as:
actual-budget
When the calculated item is added, it is almost as if the table reads the zero difference as a number, and thus includes the line, even though there isn't any source data to calculate.

 

[Mark W.]

So "Actual" and "Budget" are items of what field? In which area of the PivotTable is this field located?
This message was edited by Mark W. on 2002-03-01 14:55

 

[Mark W.]

I've been "playing around" with this data set...

{"Dept","Month","Expense","Amount"
;"A","Jan","Actual",10
;"A","Jan","Budget",18
;"B","Feb","Actual",45
;"B","Feb","Budget",30}

...and concluded that the zeros produced by =Actual-Budget for Dept A in Feb and Dept B in Jan are unavoidable.