Pivot table

[francisdsa]

item type unit(l) 1Dec16 2Dec16
Coffee Capuccino 0.2 5
Coffee Cafe Latte 0.4 5
Coffee Tea 0.2 5
Other Cake 2



I want a Pivot table to create dynamically when i add new date as below
multiple the quantity with unit when present and sum by item (0.2*5+0.4*5+0.2*5=3 for Coffee)

item sumof1Dec16 sumof2Dec16
Coffee 3
Other 2

Can someone please help me to create Pivot?

[xlnitwit]

Hi,

You need to add the calculation to the source data, unless you are using Power Pivot. A calculated field in a pivot table will calculate sum(quantity)*Sum(unit) and not sum(quantity*unit).

[PlusX.me]

If you create a table like this (which is hard to use)
1.PNG

you can have a pivot like this (you can remove the Sub field if unnecessary):
2.PNG

(Remove Grand Total too)

[francisdsa]

Francis Check table.xlsx


Thanks for the help and i manage to go this far