Forum Discussion
Creating Dynamic Weighted Average
What about this?
[Weighted NPM] =
var __denominator = SUM( T[Number of Employees] )
var __numerator =
SUMX(
T,
T[Net Profit Margin] * T[Number of Employees]
)
return
DIVIDE( __numerator, __denominator )
If the above is not what you're after.... then let's drill down into it.
You say:
As you'll see, there can be multiple subsidiaries in a parent, and multiple parents in a country and vice-versa. So I'm looking to have a weighted average which dynamically adjusts weights depending on whether I'm looking at countries or parent companies.
Could you please give a clear example of both calculations? So far you have not shown a concrete example, so it's hard to come up with a correct formula.
Hi everyone,
I managed to find a video which managed to solve my issue: https://www.youtube.com/watch?v=y7VnmZLuD5g&list=UUQ_R6vwEXkFAlw0X4P-znbw
They basically calculated the denominator using the CALCULATE(SUM([Field],ALL([Field1])) function. I think the ALL was what wasthe critical piece as with normal SUM formulas the total wasn't getting calculated properly.
Thanks for all your messages, I really appreciate it.