I have the following data:

Supplier Item# Month PO Price Std Price
ABC 123 jan 2.5 3.0
ABC 123 feb 2.5 3.0
ABC 123 feb 2.5 3.5
ABC 123 mar 3.5 3.0
XYZ 567 jan 3.0 2.0
XYZ 567 feb 3.0 2.0
XYZ 567 mar 2.5 2.0
XYZ 567 mar 2.5 2.5

I am trying to create a pivot table that will show the following:

Supplier Item # Jan price Feb price Mar price etc.
ABC 123 $2.5 $2.5 $3.5
XYZ 567 $3.0 $.3.0 $2.5

My problem:
because the standard price for ABC/123 changed during the month of Feb, I
have 2 lines for the same item# with diff. prices (same for XYZ)..so when i
create the pivot table, and use "sum of PO price" as data..the pivot table
actually give me the sum of the 2 line for Feb which = $5.0 instead of $2.5

is there any way to fix this so it will just give me $2.5 instead of $5.0?
any suggestions?

thanks,
susan

In the pivot table, right-click on one of the price cells
Click on Field Settings
Under Summarize By, click on Max, then click OK


susan wrote:
I have the following data:

Supplier Item# Month PO Price Std Price
ABC 123 jan 2.5 3.0
ABC 123 feb 2.5 3.0
ABC 123 feb 2.5 3.5
ABC 123 mar 3.5 3.0
XYZ 567 jan 3.0 2.0
XYZ 567 feb 3.0 2.0
XYZ 567 mar 2.5 2.0
XYZ 567 mar 2.5 2.5

I am trying to create a pivot table that will show the following:

Supplier Item # Jan price Feb price Mar price etc.
ABC 123 $2.5 $2.5 $3.5
XYZ 567 $3.0 $.3.0 $2.5

My problem:
because the standard price for ABC/123 changed during the month of Feb, I
have 2 lines for the same item# with diff. prices (same for XYZ)..so when i
create the pivot table, and use "sum of PO price" as data..the pivot table
actually give me the sum of the 2 line for Feb which = $5.0 instead of $2.5

is there any way to fix this so it will just give me $2.5 instead of $5.0?
any suggestions?

thanks,
susan


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

thanks debra! now does the "max" give me the maximum price within the
"group"? which then if the say XYZ/567 March if one PO price is 2.5, the
other one is 2.0, then i will only see $2.5. is there any way to show both
$2.5 and $2.0 so we can tell there is a price variance within the same
supplier/item? thanks again!

"Debra Dalgleish" wrote:

In the pivot table, right-click on one of the price cells
Click on Field Settings
Under Summarize By, click on Max, then click OK


susan wrote:
I have the following data:

Supplier Item# Month PO Price Std Price
ABC 123 jan 2.5 3.0
ABC 123 feb 2.5 3.0
ABC 123 feb 2.5 3.5
ABC 123 mar 3.5 3.0
XYZ 567 jan 3.0 2.0
XYZ 567 feb 3.0 2.0
XYZ 567 mar 2.5 2.0
XYZ 567 mar 2.5 2.5

I am trying to create a pivot table that will show the following:

Supplier Item # Jan price Feb price Mar price etc.
ABC 123 $2.5 $2.5 $3.5
XYZ 567 $3.0 $.3.0 $2.5

My problem:
because the standard price for ABC/123 changed during the month of Feb, I
have 2 lines for the same item# with diff. prices (same for XYZ)..so when i
create the pivot table, and use "sum of PO price" as data..the pivot table
actually give me the sum of the 2 line for Feb which = $5.0 instead of $2.5

is there any way to fix this so it will just give me $2.5 instead of $5.0?
any suggestions?

thanks,
susan


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

You could add another copy of the PO price field to the data area, and
set it to show the Minimum price.

susan wrote:
thanks debra! now does the "max" give me the maximum price within the
"group"? which then if the say XYZ/567 March if one PO price is 2.5, the
other one is 2.0, then i will only see $2.5. is there any way to show both
$2.5 and $2.0 so we can tell there is a price variance within the same
supplier/item? thanks again!

"Debra Dalgleish" wrote:


In the pivot table, right-click on one of the price cells
Click on Field Settings
Under Summarize By, click on Max, then click OK


susan wrote:

I have the following data:

Supplier Item# Month PO Price Std Price
ABC 123 jan 2.5 3.0
ABC 123 feb 2.5 3.0
ABC 123 feb 2.5 3.5
ABC 123 mar 3.5 3.0
XYZ 567 jan 3.0 2.0
XYZ 567 feb 3.0 2.0
XYZ 567 mar 2.5 2.0
XYZ 567 mar 2.5 2.5

I am trying to create a pivot table that will show the following:

Supplier Item # Jan price Feb price Mar price etc.
ABC 123 $2.5 $2.5 $3.5
XYZ 567 $3.0 $.3.0 $2.5

My problem:
because the standard price for ABC/123 changed during the month of Feb, I
have 2 lines for the same item# with diff. prices (same for XYZ)..so when i
create the pivot table, and use "sum of PO price" as data..the pivot table
actually give me the sum of the 2 line for Feb which = $5.0 instead of $2.5

is there any way to fix this so it will just give me $2.5 instead of $5.0?
any suggestions?

thanks,
susan


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com