Creating Dynamic Weighted Average

[Anonymous]

Hi Community,

I am trying to create a weighted average measure in Power BI. However, the weight I'm using is being driven by another unrelated column (for example, I'm trying to find the weighted average of profit margin using number of employees as the weight). I've come across a few solutions to the problem using the SUMX function but I'm not able to get the syntax to work properly. My data has hierarchy in it, so I have parent companies which have subsidiaries scattered across multiple countries.

The issue is when I put the measures into a table visual, I notice the weight for all my entries is coming as 100% as the denominator in the weight calculation doesn't take the sum of all the employees, but rather only the sum of the employees belonging to that particular country / parent. So as an example of what I'm trying to illustrate:

What I want= Profit Margin of Country X * (Employees in Country X)/(Total Employees) + Profit Margin of Country Y * (Employees in Country Y)/(Total Employees)

What I am seeing: Profit Margin of Country X * (Employees in Country X)/(Employees in Country X) + Profit Margin of Country Y * (Employees in Country Y)/(Employees in Country Y)

I'm not able to fix the value of Total Employees . So I just wanted to check whether it's possible for me to write the measure where I can find the total number of employees as a single number, which can be used when calculating the weighted average for each country. However, I need this number to be dynamic, so that if someone filters the data that total employee number gets calculated for the selection group.

I am unfortunately unable to share data due to confidentiality, but I hope I was able to explain my problem

[Anonymous]

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.

[Anonymous]

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.

[CNENFRNL]

Hi, @Anonymous , it's a bit tricky to understand what's going on under the hood for SUM(); it's a syntactic sugar for SUMX(), which is designed to make things easier to read or to express. So, the measure

Total Employees = SUM(Sheet1[Number of Employees])

corresponds to:

Total Employees = SUMX(Sheet1, Sheet1[Number of Employees])

Sheet1 is always subject to the filter context in which it's used, e.g. at subsidiary level as current level, it's filtered down to subsidiary; at parent level, it's filtered down to parent level accordingly; but to my understanding, you need a sum up at parental level of current level.

You might want to refer to this article for such a dynamic sum up of parental level.

[amitchandak]

@Anonymous , This operation need to at row level Profit Margin of Country X * (Employees in Country X) or key near to row level. If you can create a sample to test, will be ablr to help

It will be like

divide(sumx(Vaules(Table[Country]),[Profit Margin of Country X]* [Employees in Country X]),[Total Employees])

[Anonymous]

Hi @amitchandak , 

I tried using the measure which you gave but I'm still not able to get it to work. However, I've provided below a dummy dataset below (apologies for the screenshot, for some reason I can't share this as a table in the response):

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.

The measure which I had used for creating the weighted average was as follows:

My Measure = SUMX('Sheet1', Sheet1[Net Profit Margin] * (Sheet1[Number of Employees]/[Total Employees]))

Total Employees = SUM(Sheet1[Number of Employees])

However, when I look closer I realize that the weights are not calculating properly. I hope this helps further