Pivot table: configuring calculated field correctly

[jps1x2]

Hi all,

i have one problem in one pivot table. Imagine this set of data (i
have it in a sheet named PivotData in an excel book), you can search
and replace (|) for tab and paste in one excel sheet and you will see
correctly the data:

Rep|Region|Date|Item|Units|Unit Cost|Total|Stock
Andrews|Ontario|18/04/2005|Pencil|75|1,99|149,25|50
Gill|Ontario|15/01/2006|Binder|46|8,99|413,54|10
Gill|Ontario|10/09/2006|Pencil|7|1,29|9,03|50
Howard|Quebec|12/07/2005|Binder|29|1,99|57,71|10
Jardine|Ontario|09/02/2005|Pencil|36|4,99|179,64|50
Jardine|Ontario|17/11/2006|Binder|11|4,99|54,89|10
Jones|Quebec|06/01/2005|Pencil|95|1,99|189,05|50
Jones|Quebec|18/09/2005|Pen Set|16|15,99|255,84|40
Jones|Quebec|18/02/2006|Binder|4|4,99|19,96|10
Kivell|Ontario|23/01/2005|Binder|50|19,99|999,5|10
Morgan|Ontario|25/06/2005|Pencil|90|4,99|449,1|50
Morgan|Ontario|21/07/2006|Pen Set|55|12,49|686,95|40
Parent|Quebec|29/07/2005|Binder|81|19,99|1619,19|10
Smith|Ontario|01/09/2005|Desk|2|125|250|20
Smith|Ontario|01/02/2006|Binder|87|15|1305|10
Sorvino|Alberta|15/03/2005|Pencil|56|2,99|167,44|50
Sorvino|Alberta|27/09/2006|Pen|76|1,99|151,24|30
Thompson|Alberta|22/05/2005|Pencil|32|1,99|63,68|50
Thompson|Alberta|14/10/2006|Binder|57|19,99|1139,43|10

Each item has its available stock:

Blinder 10
Desk 20
Pen 30
Pen Set 40
Pencil 50


But when i make a pivot table with this information, in row section it
will be following fields:

Rep|Region|Date|Item

and in data section fiels:

Units|Unit Cost|Total|Stock

if i see the pivot table aggregated information at Rep level, or Rep|
Region level, Pivot table multiplies Stock by the times it has been
sold in that Rep or Rep|Region.

When i expanded at level Rep|Region|Date|Item then stock is correctly
because then it shows each row without aggregating them.

Is there any way of showing stock correctly when i see aggregated
information or expanded at low level? I don't know how to use
calculated fiels, but i think than using advanced functions in a
calculated field i could do this.

If you know any tutorial about advanced pivot tables about calculated
fields or calculated items it will be fantastic.

Sorry, but my english is not very good.

Gracias / Thank you very much

Jorge

 

[Ed Ferrero]

Hi Jorge,

Right-click the field heading for Stock,
Select Field Settings from the drop-down menu,
Change 'Summarize by' from Sum to Min.

Look at my Pivot Table tutorial for more
http://www.edferrero.com/ExcelTutorials/tabid/54/Default.aspx

And look at Debra Dalgleish's site on Pivot Tables
http://www.contextures.com/xlfaqPivot.html

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

 

[jps1x2]

Thanks Ed,

when i try to see your pivot table tutorial, i obtain the following
error:

Server Error in '/' Application.
--------------------------------------------------------------------------------
The resource cannot be found.

Pivot table doesn't work correctly with your solution because if you
see the data:

Gill|Ontario|15/01/2006|Binder|46|8,99|413,54|10
Gill|Ontario|16/01/2006|Binder|46|8,99|413,54|10
Gill|Ontario|10/09/2006|Pencil|7|1,29|9,03|50

Binder has an stock of 10 and Pencil has an stock of 50, but if i see
aggregated information at Rep level it shows 10 on stock field, and it
would be 60 (10 of Blinder and 50 of Pencil).

If i would like to put another field for the item, the field colour,
for example all fields of the pivot table will be in row section:

Rep|Region|Date|Item|Colour

And the data will be:

Gill|Ontario|15/01/2006|Binder|Beige|46|8,99|413,54|15
Gill|Ontario|16/01/2006|Binder|Beige|46|8,99|413,54|15
Gill|Ontario|17/01/2006|Binder|White|46|8,99|413,54|10
Gill|Ontario|10/09/2006|Pencil|Black|7|1,29|9,03|50
Gill|Ontario|11/09/2006|Pencil|White|7|1,29|9,03|60
Gill|Ontario|12/09/2006|Pencil|Black|7|1,29|9,03|50

And when i have expanded at all (Rep|Region|Date|Item|Colour) the
stock is ok, but when i have agreggated not at all level (Rep|Region|
Date|Item) it would be interesting that Stock field to summarize, for
each item, all the stock of different colours of that item.

Muchas Gracias / Thank you very much.

Jorge

 

[Ed Ferrero]

Thanks for the feedback Jorge,

Please try again. The website is being moved to a new server this Thursday.
It should no longer give these errors after that.

Ed Ferrero
www.edferrero.com

 

[jps1x2]

Hi Ed,

i can't see "Pivot table tutorial excel 2003", i obtain the following
error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

The resource cannot be found.
Description: HTTP 404. The resource you are looking for (or one of its
dependencies) could have been removed, had its name changed, or is
temporarily unavailable. Please review the following URL and make
sure that it is spelled correctly.

Requested URL: /Pivot200301.aspx

Thanks