Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Creating Dynamic Weighted Average

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...
DAX
Measures
amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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