I cannot find a way to create a calculated field in pivot table that mutiplies 2 columns (eg Qty * price)
I want to calculate Qty * price for each row and sum the result.
Instead I get Sum of Qty * Sum of Price
Have tried sumproduct
See Example below
Table
Product Qty Price Required result
a 2 10 20
a 5 23 115
d 6 21 126 Values
f 7 45 315 Row Labels Sumof Field1
r 9 67 603 a 231 231 231
c 4 56 224 b 28 28 28
b 7 4 28 c 224 224 224
d 8 78 624 d 1386 1386 1386
t 9 9 81 f 477 477 477
f 2 8 16 r 603 603 603
t 81 81 81
Sum product 2152 2152 231 231 231
Grand Total 23364 23364 23364
Total product s/ba 135
d 750 Field1 =Qty*Price
Field2 = SUMPRODUCT(Qty,Price)
7 33 231 Field3 = SUM(Qty*Price)
I want to calculate Qty * price for each row and sum the result.
Instead I get Sum of Qty * Sum of Price
Have tried sumproduct
See Example below
Table
Product Qty Price Required result
a 2 10 20
a 5 23 115
d 6 21 126 Values
f 7 45 315 Row Labels Sumof Field1
r 9 67 603 a 231 231 231
c 4 56 224 b 28 28 28
b 7 4 28 c 224 224 224
d 8 78 624 d 1386 1386 1386
t 9 9 81 f 477 477 477
f 2 8 16 r 603 603 603
t 81 81 81
Sum product 2152 2152 231 231 231
Grand Total 23364 23364 23364
Total product s/ba 135
d 750 Field1 =Qty*Price
Field2 = SUMPRODUCT(Qty,Price)
7 33 231 Field3 = SUM(Qty*Price)
