Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculated Item in Pivot Table Adds Rows with Zero Values
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Item in a Pivot Table | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
PIVOT TABLE - hiding records with CALCULATED item values equal to | Excel Discussion (Misc queries) |