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.
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.