Pivot Table


S

susan

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
 
Ad

Advertisements

D

Debra Dalgleish

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
 
S

susan

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!
 
Ad

Advertisements

D

Debra Dalgleish

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

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