Calculated item - problem

[Victorinox]

Hey,

I have a problem with my calculated item in my Pivot Table.

The Pivot Table is as follows

2007 2008
Country 1 Area A 43 50
Area B 60 70
Country 2 Area X 76 89
Area Y 34 34

I want to add a index which is 2008/2007. Therefore I used a calculated
item. But the problem is that I get the following result


2007 2008 index
Country 1 Area A 100 50 0,5
Area B 60 70 ...
Area X Error
Area Y Error
Country 2 Area A Error
Area B Error
Area X 76 89 ...
Area Y 34 34 ....

My problem: How do I avoid that I receive for instance that Area X and Area
Y appears in Country 1. First of all it makes no sense since they are not
part of this country, but the biggest problem is that it makes my Pivot not
clear anymore, because I have al lot of countries & a lot of area's...

Adding an extra row in my data source is not an option.

Thx for the help,

 

[Debra Dalgleish]

Unfortunately, that's a characteristic of Calculated Items that you
can't change.
Instead of a Calculated Item, you could add another copy of the data
field to the data area.
Then, right-click on the heading for the second data field, and click on
Field Settings
Click the Options button
For 'Show Data As:' select % Of
For the Base Field, select Year
For the Base Item, select 2007, click OK

You'll end up with an extra column, but you could hide that, or ignore it.

You can change the PivotTable options to hide the errors, if necessary.

 

[Herbert Seidenberg]

Excel 2007
Auxiliary Table added to PT with VBA.
Allows for more complex calculations:
http://www.mediafire.com/file/jzxn0y0j4tl/12_30_08a.xlsm

 

[Victorinox]

Thx problem solved.

Unfortunately, that's a characteristic of Calculated Items that you
can't change.
Instead of a Calculated Item, you could add another copy of the data
field to the data area.
Then, right-click on the heading for the second data field, and click on
Field Settings
Click the Options button
For 'Show Data As:' select % Of
For the Base Field, select Year
For the Base Item, select 2007, click OK

You'll end up with an extra column, but you could hide that, or ignore it.

You can change the PivotTable options to hide the errors, if necessary.



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com