Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table: How do I hide calculated items that result in zero? | Charts and Charting in Excel | |||
Pivot Table Size | Charts and Charting in Excel | |||
Calculated field in pivot table | Excel Discussion (Misc queries) | |||
Excel 2000 Pivot Table Calculated Fields | Excel Discussion (Misc queries) | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions |