How to calculate weighted average from a same column

[Jeff_Ng]

Hi,

Hope you can help me with this. Many thanks in advance. 

I want to create a measure in Power BI to calculate weighted average diameter from a same column/table as shown in table below. This measure must be able to be filtered by Date and Reach in the line chart.   

[Anonymous · Accepted solution]

Hello @Jeff_Ng ,

In power query editor, pivot the Item column using values Units.

Then in report create a measure

Measure = (SUM(TableName[Pipe Length(km)])*sum(TableName[Diameter(mm)]))/SUM(TableName[Pipe Length(km)])