Pivot Table - Problems when using Calculated Item

S

sg

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
 
D

Debra Dalgleish

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top