topic Re: Calculate weighted average based on another field in DAX Commands and Tips

Calculate weighted average based on another field

SrinivasK — Thu, 29 Aug 2024 19:51:19 GMT

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

Re: Calculate weighted average based on another field

ahadkarimi — Thu, 29 Aug 2024 19:53:03 GMT

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!

Re: Calculate weighted average based on another field

SrinivasK — Fri, 30 Aug 2024 05:42:07 GMT

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.