Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculated item - problem
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculated item - problem
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. Victorinox wrote: 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 Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculated item - problem
Excel 2007
Auxiliary Table added to PT with VBA. Allows for more complex calculations: http://www.mediafire.com/file/jzxn0y...12_30_08a.xlsm |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculated item - problem
Thx problem solved.
"Debra Dalgleish" wrote: 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. Victorinox wrote: 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 Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to create a calculated item based on a calculated field | 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) | |||
Calculated Item | Excel Worksheet Functions |