pivot table sum of column divide by count of another column

[iknowu99]

Hi All, on the left, row labels, is weekday. Center pivot table has sum of sales and count of vendors as two columns. I know i can copy values to different space and divide the two but perhaps it's possible inside the pivot table?

 

[taigovinda]

I tinkered with this and couldn't figure it out... Only way I could see to do is add a column to the raw data and fill it with 1's. Obviously after that you can calculated field that is sum of sales divided by sum of 1's...

Excel Workbook
	A	B	C	D
1	Weekday	Vendor	VendorCount	Sales
2	Monday	ABC	1	100
3	Monday	ABC	1	100
4	Monday	DEF	1	50
5	Monday	DEF	1	50
6	Monday	DEF	1	50
Sheet1

 

[iknowu99]

Data:

Vendor ProductCost Dates
A 300 1/1/2011
B 400 1/3/2011
C 500 1/6/2011
A 400 1/5/2011
E 500 1/1/2011
A 600 1/12/2011
G 700 1/15/2011




Result Pivot table is:

RowLabels SumofProductCost CountofDates
A 1300 3
B 400 1
C 500 1
E 500 1
G 700 1
Grand Total 3400 7



in the resulting table how to have sumofProductCost divide by CountofDates inside the pivot table options?

 

[iknowu99]

bump

 

[DonkeyOte]

Not sure I follow - wouldn't you just add Product Cost to the Data field a second time setting as Average (rather than Sum) - you can use a source field as a Data field more than once

 

[iknowu99]

these pivots get pretty massive and with all data forgot what i was after....gotta laugh at it.....averages