How to multiply the total value inside the pivot table

[msmfashan]

Hi,

Can any one help me to multiply the qty times price inside the pivot table using the fields,items & sets. below is the data.

EXCEL SHEET
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]INVOICE NUMBER[/TD]
[TD]BARCODE [/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]AX[/TD]
[TD]10[/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]AY[/TD]
[TD]15[/TD]
[TD]19.6[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]AZ[/TD]
[TD]15[/TD]
[TD]18.6[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]BB[/TD]
[TD]10[/TD]
[TD]15.5[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]BD[/TD]
[TD]20[/TD]
[TD]10.5[/TD]
[/TR]
</tbody>[/TABLE]

PIVOT TABLE[TABLE="width: 500"]
<tbody>[TR]
[TD]INVOICE NUMBER[/TD]
[TD]QTY[/TD]
[TD]TOTAL PRICE[/TD]
[/TR]
[TR]
[TD]368[/TD]
[TD]70[/TD]
[TD]5719(WRONG)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Regards,
FS

 

[Comfy]

Add a total column to your source Data.

Excel 2010
	A	B	C	D
2
3	Row Labels	Sum of Qty	Sum of Total	Sum of Total2
4	368	70	1113	5719
5	AX	10	175	175
6	AY	15	294	294
7	AZ	15	279	279
8	BB	10	155	155
9	BD	20	210	210
10	Grand Total	70	1113	5719
11
Sheet4

 

[msmfashan]

add a total column to your source data.

excel 2010

	a	b	c	d
row labels	sum of qty	sum of total	sum of total2
368
ax
ay
az
bb
bd
grand total

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]2[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]3[/td]

[td="align: Center"]4[/td]

[td="align: Right"]70[/td]
[td="align: Right"]1113[/td]
[td="align: Right"]5719[/td]

[td="align: Center"]5[/td]

[td="align: Right"]10[/td]
[td="align: Right"]175[/td]
[td="align: Right"]175[/td]

[td="align: Center"]6[/td]

[td="align: Right"]15[/td]
[td="align: Right"]294[/td]
[td="align: Right"]294[/td]

[td="align: Center"]7[/td]

[td="align: Right"]15[/td]
[td="align: Right"]279[/td]
[td="align: Right"]279[/td]

[td="align: Center"]8[/td]

[td="align: Right"]10[/td]
[td="align: Right"]155[/td]
[td="align: Right"]155[/td]

[td="align: Center"]9[/td]

[td="align: Right"]20[/td]
[td="align: Right"]210[/td]
[td="align: Right"]210[/td]

[td="align: Center"]10[/td]

[td="align: Right"]70[/td]
[td="align: Right"]1113[/td]
[td="align: Right"]5719[/td]

[td="align: Center"]11[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

</tbody>

sheet4​

thanks for the reply, but i need to give a formula inside the pivot table to multiply any tips.

 

[Comfy]

thanks for the reply, but i need to give a formula inside the pivot table to multiply any tips.

I don't think you can.

It would require an array formula and I don't think Pivot Tables can do this.