Calculated Item in Pivot Table Adds Rows with Zero Values

M

Marc Forget

I have read through previous threads and still cannot resolve my problem.
When I add a calculated item to my pivot table, it calculates the values
properly but displays all possible combinations of my row fields, showing
extraneous lines with 0 values (see below).

I tried Roger Govier's suggestion to add a formula similar to
=IF(SUM('Actual')-SUM('Budget Number')=0,"",SUM('Actual')-SUM('Budget
Number')) but then I get a #VALUE! error instead of zeroes and the additional
lines still come up.

My data looks like this before I add a calculated item:

Country Region Mar 07 Apr 07 May 07
US US East Revenue 1,945,758 2,094,821 2,546,369
Sales All. 29,529 (78,556) (95,489)
JE's 25,000 - -
US West Revenue 2,498,178 1,784,177 1,703,763
Sales All. 29,636 (66,907) (63,891)
JE's 3,509 - -

When I add a calculated item equal to 'May 07' - 'Apr 07', I get additional
lines which should not exist (i.e. combinations of Country and Region which
do not make sense) with zeroes:

Country Region Mar 07 Apr 07 May 07 Change
US Paris Revenue 0 0 0 0
Sales All. 0 0 0 0
JE's 0 0 0 0

And if I try the formula =IF(SUM('May-07')-SUM('Apr-07')=0,"
",SUM('May-07')-SUM('Apr-07')), then the zero values under "Change" become
"#VALUE!."

If anybody has a solution, I would greatly appreciate it.

Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top