Weighted Average in Excels pivot table

Question

I have a table with 4 columns. Variable1, Variable2, Kpi1 and Kpi2.

Variable1 is one level above Varible2 (i.e, variable1 is the parent of variable2).

Kpi1 is an integer and Kpi2 is a float [ range (0,1) ].

When making a pivot table, variable2 looks fine with its values, but the column total (variable1) doesn't. Kpi2 can't be calculated as a simple sum or a simple average of its values of variable2. It needs to be a weighted average of it using kpi1.

To make it more clear I will leave here an example I did on Excel.

Is there any form I can achieve this?

Answer 1

You will need to add a helper column & a calculated field to pivot table to do this.

---

New Table Column = Product = kp1 * kp2  
Calculated Field = Weight = Product / kp1

---

---

You can add or remove fields from the pivot table once completed