How to calculate weighted average in Pivot Table in Excel

Problem

We would like to calculate the weighted average in a pivot table in Excel.

In our example, using the mtcars data set, we want to calculate the mpg average weighted by dips.

Solution

Let's see two alternatives:

Option1: Adding a column.

1. We add a new columng multiplying the variable (mpg) by the weights (disp)

2. We create a Calculated Field dividing the new column (mpg*disp) by the weights (disp)

3. We create a pivot table adding the Calculated Field.

Option 2: Using a DAX functions.

1. We add the table to the Data Model.

2. We create a new Measure.


weighted_average:=SUMX( Table1,[mpg] * [disp]) / SUM([disp])

3. We create a pivot table from Data Model as a data source adding the previous Measure.

Related posts

• Media aritmética ponderada en R

References

• mtcars data set