Pivot Table - Problems when using Calculated Item
Hi there - I am having a problem with my pivot table when i use a calculated
item. The problem is, when i use the calculated item option, it ignores my PAGE FIELD and returns ALL records. The pivot table has a page field, for example MARITIMES. And i want all the people FROM the Maritimes, who sold APPLES and ORANGES returned on the pivot. At this point things are working good. Sample 1: PAGE FIELD: MARITIMES ..............................PRODUCTS............ ......... NAME.................APPLES.........ORANGES Mr Jones................5.........................9.. ......... Ms Smith................7.........................7.. ........ Problem occurs when i add a CALCULATED ITEM. The calculated item takes 50% of the apples + 50% of oranges. The calculations work fine, however, it seems to ignore my PAGE FIELD (Maritimes), and returns ALL records. Only people in the MARITIMES sell apples and oranges, therefore all other records that appear, correctly show 0 values but they shouldn't be displayed in the first place. Sample 2: PAGE FIELD: MARITIMES ..............................PRODUCTS............ .................................. NAME.................APPLES.........ORANGES....... ..BLEND.... Mr Jones................10........................9.. ..................=Apples*50%+Oranges*50% = 9.5 Ms Smith................7.........................7.. ...................=Apples*50%+Oranges*50% = 7 Mr Tom..................0.........................0.. ..................=Apples*50%+Oranges*50% = 0 Mr King..................0.........................0. ...................=Apples*50%+Oranges*50% = 0 Ms Queen...............0.........................0... .................=Apples*50%+Oranges*50% = 0 Any help would be appreciated!!! Sandi |
Pivot Table - Problems when using Calculated Item
Since there's a calculation for each person, even if it's zero, each
person will appear in the filtered pivot table. Perhaps you could add a column to the source data, and do the calculation there. Then, add the new field to the data area, and it should only show the records for the selected region. sg wrote: Hi there - I am having a problem with my pivot table when i use a calculated item. The problem is, when i use the calculated item option, it ignores my PAGE FIELD and returns ALL records. The pivot table has a page field, for example MARITIMES. And i want all the people FROM the Maritimes, who sold APPLES and ORANGES returned on the pivot. At this point things are working good. Sample 1: PAGE FIELD: MARITIMES .............................PRODUCTS............. ........ NAME.................APPLES.........ORANGES Mr Jones................5.........................9.. ......... Ms Smith................7.........................7.. ........ Problem occurs when i add a CALCULATED ITEM. The calculated item takes 50% of the apples + 50% of oranges. The calculations work fine, however, it seems to ignore my PAGE FIELD (Maritimes), and returns ALL records. Only people in the MARITIMES sell apples and oranges, therefore all other records that appear, correctly show 0 values but they shouldn't be displayed in the first place. Sample 2: PAGE FIELD: MARITIMES .............................PRODUCTS............. ................................. NAME.................APPLES.........ORANGES....... ..BLEND.... Mr Jones................10........................9.. ..................=Apples*50%+Oranges*50% = 9.5 Ms Smith................7.........................7.. ...................=Apples*50%+Oranges*50% = 7 Mr Tom..................0.........................0.. ..................=Apples*50%+Oranges*50% = 0 Mr King..................0.........................0. ...................=Apples*50%+Oranges*50% = 0 Ms Queen...............0.........................0... .................=Apples*50%+Oranges*50% = 0 Any help would be appreciated!!! Sandi -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 03:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com