How to do a weighted average in my pivot table that follows a filtering criteria?

Question

I have an idea how to create a simple weighted average, but what makes this application a bit confusing for me is because the weights are based on a ratio--where its denominator is a sum of all rows that contain the same name.

I think it has something to do with calculated fields or DAX but I have no experience on how to formulate it on Excel.

Please refer to image below:

Answer 1

You will need to first add this table to the Power Pivot data model. I will call this table "Data".

Then, create a DAX measure:

Weighted Average = 
DIVIDE ( 
    SUMX ( Data, Data[Production Quantity] * Data[Average] ), 
    SUM ( Data[Production Quantity] ) 
    )

Create a pivot table with Name on the rows, drop the measure into it, and you will get this result:

(please note that your example shows incorrect calculations for Name D).