The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 🙂
Solved! Go to 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
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! 🙂
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
Thanks!!! 🤗
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
Thanks for the help 🤗
User | Count |
---|---|
24 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
9 | |
9 |