I wanted to calculate monthly weighted average in powerBI

Question

I have a table that has PIMS code of crudes and first day of the month, its cost(Rs/MT) and quantity Thousand MT to be processed. I need to calculate the weighted average of cost(Rs/MT) based on PIMS code and for that month only.

In the table, as you can see there are double entries of PIMS code with different quantity and price but with the same date and that difference needs to be considered while doing the average so I want to get a weighted average.

Answer 1

You can create a Weighted Average measure using the Quick measure functionality. You can do a basic google search for this as well.

Here is the documentation for creating a Quick Measure.

Answer 2

The following should work (Assuming your table is called 'Data') Weighted Avg:= VAR TotalUnits=SUM(Data[Quantity (TMT)]) VAR TotalCost=SUMX(Data,Data[cost(Rs/MT)]*Data[Quantity (TMT)]) RETURN TotalCost/TotalUnits

Screenshot below to show examples with some dummy data