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/ExcelTutori...4/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

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