Pivot table calculated field is acting weird!

[INN]

Hi
I added a calculated filed called Total which multiples Unit * Price. When I change the function of each Unit and Price from Sum to Count for example for item A, I still get 60 but not 6. Why is that? and Why 60, should not be 30 if I mutliple the Unit * Price (3 * 10). Thank you very much.

test.xlsx
	A	B	C	D	E	F	G	H	I	J
14	Name	Item	Unit	Price	Region		Row Labels	Count of Price	Sum of Unit	Sum of Total
15	Mary	A	1	10	East		A	2	3	60
16	Linda	A	2	10	West		B	1	1	5
17	Raj	B	1	5	West		C	1	1	10
18	Jack	D	1	5	East		D	1	1	5
19	Bob	C	1	10	Cental		Grand Total	5	6	240
Sheet4

 

[DRSteele]

Yup, it seems weird. I added a record to the data to make it easier to understand.

Here is what's happening with the calculated field in the PT:
The PT is summing the Price per Item, even though you think it's counting them. That's 10 +10 +53 = 73 price total. This field doesn't even need to be in the PT to make things go wonky.
It is then summing the Unit per Item. That's 1 + 2 + 12 = 15 units.
Then it multiplies them: 15 * 73 = 1095

It might be better to append your Table to have a field that multiplies the Unit field by the Price field.

{The rules of the Forum state that you aren't supposed ask the same question several times, as you did here and here. Naughty!}

MrExcel posts19.xlsx
	D	E	F	G	H	I	J	K	L
4	ITEM	UNIT	PRICE			Row Labels	Count of PRICE	Sum of UNIT	Sum of Total
5	A	1	10			A	3	15	1095
6	A	2	10			B	1	1	5
7	B	1	5			C	1	1	10
8	D	1	5			D	1	1	5
9	C	1	10			Grand Total	6	18	1674
10	A	12	53
11
12
13	ITEM	UNIT	PRICE	revenue		Row Labels	Count of PRICE	Sum of UNIT	Sum of revenue
14	A	1	10	10		A	3	15	666
15	A	2	10	20		B	1	1	5
16	B	1	5	5		C	1	1	10
17	D	1	5	5		D	1	1	5
18	C	1	10	10		Grand Total	6	18	686
19	A	12	53	636
Sheet33

Cell Formulas
Range	Formula
G14:G19	G14	=[@UNIT]*[@PRICE]