E
Eric D
Why do extraneous rows show up in pivot table with
calculated item?
Scenario:
I create a pivot table with 2 row headings (location,
department) then add 1 column (month) and 1 data
(headcount). Then I create a "calculated item" (gain) on
the column (month) of "=February-January".
When it adds it to the pivot table I get extraneous
calculations for "departments" that don't exist in
certain "locations". It returns a zero. What I need is
for those "departments" not to appear at all.
Anyone recognize this? Any solutions?
Thanks
Eric
Example
City Department Month Headcount
Dallas Sales January 10
Dallas Marketing January 20
Dallas Sales February 15
Dallas Marketing February 25
San Diego IT January 10
San Diego HR January 20
San Diego IT February 15
San Diego HR February 25
Pivot Table
Sum of Headcount Month
City Department January February
Gain
Dallas HR 0
IT 0
Marketing 20 25 5
Sales 10 15 5
Dallas Total 30 40 10
San Diego HR 20 25 5
IT 10 15 5
Marketing 0
Sales 0
San Diego Total 30 40 10
Grand Total 60 80 20
calculated item?
Scenario:
I create a pivot table with 2 row headings (location,
department) then add 1 column (month) and 1 data
(headcount). Then I create a "calculated item" (gain) on
the column (month) of "=February-January".
When it adds it to the pivot table I get extraneous
calculations for "departments" that don't exist in
certain "locations". It returns a zero. What I need is
for those "departments" not to appear at all.
Anyone recognize this? Any solutions?
Thanks
Eric
Example
City Department Month Headcount
Dallas Sales January 10
Dallas Marketing January 20
Dallas Sales February 15
Dallas Marketing February 25
San Diego IT January 10
San Diego HR January 20
San Diego IT February 15
San Diego HR February 25
Pivot Table
Sum of Headcount Month
City Department January February
Gain
Dallas HR 0
IT 0
Marketing 20 25 5
Sales 10 15 5
Dallas Total 30 40 10
San Diego HR 20 25 5
IT 10 15 5
Marketing 0
Sales 0
San Diego Total 30 40 10
Grand Total 60 80 20