Weighted average formula help

[dgch]

Hello everyone, i need help with the weighted average formula in Power BI, let me explain:

I have this table:

Table1

The columns are month, workcenter, rework% generated by workcenter, and the tons rework generated. I need to calculate the weighted average of rework% weighted by tons, group by month, and the result should be something like this:

Could somebody help me to achieve this, thanks 🙂

[az38 · Accepted solution]

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

[JarroVGIT · Accepted solution]

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:

Month	Workcenter	Percentage	Tonnes
A	1	10	100
A	2	15	150
B	1	5	200
B	2	10	150
C	1	1	300
C	2	2	150

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! 🙂

[dgch]

Thanks!!! 🤗

[az38 · Accepted solution]

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

[dgch]

Thanks for the help 🤗