Forum Discussion
Calculate weighted average based on another field
I have product wise volume and certain budget allocated on it but need to find prorpotion of this budget by calculating share of volume in total volume.
I tried few variations of this - using Allselected or All because there were few filters working on this visual matrix table. But unable to get the result shown in weighted average column below. Can any expert help ?
Weighted Avg =
var totalvol = calculate(sum(table[Vol]),All (table)),
Return
Sumx(
All (table),
Divide ((table[Vol]), totalvol) * (table[Budget]))
Ex:
| Product | Vol | Budget | Weighted Avg (Result) |
| A | 200 | 50 | 12.5 ((200/800)*50) |
| B | 200 | 50 | 12.5 |
| C | 200 | 48 | 12 |
| D | 200 | 48 | 12 |
| Total | 800 |
Hi SrinivasK , try this measure below, and if you encounter any issues, let me know.
Weighted Avg = VAR TotalVol = CALCULATE(SUM(Table[Vol]), ALL(Table)) RETURN SUMX( Table, DIVIDE(Table[Vol], TotalVol) * Table[Budget] )Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!Thanks ahadkarimi for your answer. I changed All to Allselected on Total var and it worked. I guess since there are filters applied, on the matrix, the allselected worked.
2 Replies
Hi SrinivasK , try this measure below, and if you encounter any issues, let me know.
Weighted Avg = VAR TotalVol = CALCULATE(SUM(Table[Vol]), ALL(Table)) RETURN SUMX( Table, DIVIDE(Table[Vol], TotalVol) * Table[Budget] )Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!Thanks ahadkarimi for your answer. I changed All to Allselected on Total var and it worked. I guess since there are filters applied, on the matrix, the allselected worked.