Pivot table calculate column, not right total

[INN]

Hi
I have the table below. I created a ca;cu;ated colum in Pivot table to multiple Unit * Price. However the total that Pivot table is giving me is 40 but in excel if I multiple Unit* Price for all items using Sumproduct or writing simple formula then I would give 10, So which result is right and which one is wrong. Thank you very much.

Relationship.xlsx
	A	B	C	D	E	F	G
1	Date	Region	Rep	Item	Color	Unit	Price
2	02/01/22	West	Kumar	Binders	Black	1	1
3	02/01/22	East	Walton	Pens	Black	1	2
4	02/01/22	Central	Jones	Desk	Black	1	3
5	02/01/22	Central	Jones	Binders	Red	1	4
6
7				total using sumproduct	10
8
9
10	Pivot table
11	Row Labels	unit #	sum price	Unit*Price (calculated col)
12	Binders	2	5	10
13	Desk	1	3	3
14	Pens	1	2	2
15	Grand Total	4	10	40
diff col (2)

Cell Formulas
Range	Formula
E7	E7	=SUMPRODUCT(F2:F5,G2:G5)

 

[gelu]

Hi Inn,


The pivot treats the 2 binders as one, sums the price then multiplies by 2.
Solution is to have an unique name, for items with different prices.

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Region	Rep	Item	Color	Unit	Price	Item-Color
2	44563	West	Kumar	Binders	Black	1	1	Binders-Black
3	44563	East	Walton	Pens	Black	1	2	Pens-Black
4	44563	Central	Jones	Desk	Black	1	3	Desk-Black
5	44563	Central	Jones	Binders	Red	1	4	Binders-Red
6
7
8
9
10	Pivot table
11	Row Labels	unit #	sum price	Unit*Price (calculated col)		Row Labels	Sum of Price	Sum of Unit	Sum of Rev
12	Binders	2	5	10		Binders-Black	1	1	1
13	Desk	1	3	3		Binders-Red	4	1	4
14	Pens	1	2	2		Desk-Black	3	1	3
15	Grand Total	4	10	40		Pens-Black	2	1	2
16								4	10
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=D2&"-"&E2
H16:I16	H16	=SUM(H12:H15)

The calculated field for grand totals does 4 x 10, as instructed (unit x price). It does not realise it is a grand total, etc.
Solution - get rid of Pivot's Grand Totals, add underneath.

calculated fields are tricky in such situation (non-unique items).

G