Weighted average formula help

Hi dgch 

you could create a calculated table

Table = 
SUMMARIZE('Table','Table'[Month],
"Ton",SUM('Table'[Ton]),
"Weighted %",DIVIDE(SUMX('Table',[Rework]*[Ton]),SUM('Table'[Ton]))
)

then do not forget to set Format: Percentage in Modeling ribbon for "Weighted %" field

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

Hi dgch 

Please post your tables in a format so we can import it into PowerBI, not as a picture, then we can help you a lot faster.

In this case, I recreate a dummy table with the following data:

The numbers and stuff doesn't really matter, it is the logic that matters. You didn't specifify what you want your result to be in, so I assumed you were fine with a calculated table. The dax for this calculated table is the following:

CalculatedTable = 
VAR _weightTable = ADDCOLUMNS('Table', "weight", DIVIDE([Tonnes], CALCULATE(SUM('Table'[Tonnes]), FILTER('Table', 'Table'[Month] = EARLIER('Table'[Month])))))
RETURN
SUMMARIZE(_weightTable, 'Table'[Month], "WeightedAveragePercentage", SUMX(FILTER(_weightTable, [Month] = EARLIER([Month])), [weight] * [Percentage]), "Tonnes", SUMX(FILTER(_weightTable, [Month] = EARLIER([Month])), [Tonnes]))

In the first VAR, I take the original table and add a weight column per month. In the RETURN section, I create a summary table based on month by summing the weight*percentage column and summing the tonnes column. This result in the following table in Power BI:

This is correct according to my manual calculations in Excel 🙂 

Let me know if this answers your question!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂