PIVOTTABLE: Calculated item causes incorrect row label.

[albatr0n]

Hey!

SOURCE DATA: I basically have agent names and their respective regions in columns as source data. For each agent there are records of value he has received or given throughout time. An agent can give or receive value. The amount of value is recorded in the "Value" column and next to it there is an "Event" column with only two possible states ('To' or 'From'). If an agent has received, the event is 'To', if an agent has given value, the event is 'From'.

I am using a PivotTable to represent the data this way:

ROW LABELS: Regions and the agents that belong to them are Row Labels (Regions on top of Agents)
COLUMN LABELS: The event (To or From) is used as Column Label; value received is in Column1, value given is in Column2
VALUES: Values received and given are summed under their respective columns against the names of their respective agents

This way the table looks great, and everything is sorted perfectly.

The problem comes when I create a Calculated item to subtract the "From" column from the "To" column to calculate the balance for each agent. Since the the two are items that belong to a single field, a new "To-From" item must be created. When the new item is added however, it causes all agent names to be displayed under all regions.

In other words, the calculated item adds unnecessary and incorrect rows of data which do not correspond to source data.

Please look at the attached workbook to see for yourself.

CalcItemRowError.xlsx

Am I doing something wrong or is this a bug?!

[albatr0n]

Please, guys, can you at least confirm the error by testing a calculated item yourself?

Thanks!

[canapone]

Hi,

I'm a pivot table user on Excel 2000 and I'd suggest to avoid the use of calculated fields: my opinion.

As far as I know it's not an error from you.

I would calculate the difference out of the pivot table or add an helper column to your data and make it read to pivot table itself.


Hope that helps

[JosephP]

it's not a bug as such-just an annoying feature. add a new column to the table that makes your From values negative then simply add row totals-see attached file

[albatr0n]

CANAPONE,

I know I can do that in a separate column, but every time new data is added or the layout of the PivotTable changes, I will have to adjust the column accordingly. Unfortunately, that's not very practical, but thanks for your suggestion. It looks like Calculated Items are a pain in the *ss.

it's not a bug as such-just an annoying feature. add a new column to the table that makes your From values negative then simply add row totals-see attached file

From what i can see in your example, adding a column with negative numbers will help avoid using Calculated items. This works, but I thought a PivotTable would handle this just fine without having to change the source data. I guess I was wrong.

JP, are you aware of why Calculated Items present the data the way they do? Is there a particular reason they work differently from standard PivotTable items?

Thanks.

[JosephP]

I don't know but would imagine it has to do with how the information is added to/stored in the pivotcache.