Calculated field in pivottable

[T3Romeo]

Hi all,

I am looking for a way to make an extra calculated field in a pivot table. This is for a monthly report I need to make. (every month I need to pay someone for the activities he did. Therefore, I created a table and from that table a pivot table (and I want to make a chart from that later)
Now my question. I would like to put a third column in the pivot, in that column the amount I need to pay.
I can make it with a formula next to the pivot like =IF(A3="Cleaning";B3*150;IF(A3="Driving";B3*100;B3*50)) but than it is not in the pivot, and that is what i need.

Can somebody help me out?

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Activity	Counts					Costs per service
3		Cleaning	6					Cleaning	€ 150.00
4		Driving	20					Driving	€ 100.00
5		Walking	3					Walking	€ 50.00
6		Running	12					Running	€ 50.00
7		Singing	9					Singing	€ 50.00
8
9
10							TO PAY
11					Row Labels	Sum of Counts
12					Cleaning	6
13					Driving	20
14					Running	12
15					Singing	9
16					Walking	3
17					Grand Total	50
18
Sheet1

 

[Alex Blakenburg]

It would be much easier to use a VLookup to add the rate / cost to the underlying table and then pivot it. See XL2BB below.

A standard pivot table can't do what you want. So otherwise you would either need to load it into the data model and use Power Pivot or else use Power Pivot.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Activity	Counts	Rate	Cost			Costs per service
3		Cleaning	6	150	900			Cleaning	150
4		Driving	20	100	2000			Driving	100
5		Walking	3	50	150			Walking	50
6		Running	12	50	600			Running	50
7		Singing	9	50	450			Singing	50
8
9
10						Row Labels	Sum of Counts	Sum of Cost
11						Cleaning	6	900
12						Driving	20	2,000
13						Running	12	600
14						Singing	9	450
15						Walking	3	150
16						Grand Total	50	4,100
17
Sheet1

Cell Formulas
Range	Formula
D3:D7	D3	=VLOOKUP([@Activity],$H$3:$I$7,2,FALSE)
E3:E7	E3	=[@Counts]*[@Rate]