Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago
Solved

Weighted Average

Hello,

 

I am trying to calculate a weighted average between multiple different fields that all have set weights on them and my calculation isn't quite calculating correctly.

 

The values that currently goes into the weighted average is a percentage of a goal that is calculated as such:

 

Measure1 =
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Table', [Value]), FILTER('Table2', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Value]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)
 
The catch with this is that if the percentage of the goal is above 150%, then the value maxes out at 150%, which is why I include the IF statement. This is also repeated for 3 different Groups of ID values
 
From there my weighted average of the percentages is calculated like this:
 
Weighted Average = 
VAR _group1 = CALCULATE(SUMX('Table', [Measure1]*0.3), FILTER('Table2', [ID)] = [ID]))
VAR _group2 = CALCULATE(SUMX('Table', [Measure2]*0.25), FILTER('Table2', [ID)] = [ID]))
VAR _group3 = CALCULATE(SUMX('Table', [Measure3]*0.45), FILTER('Table2', [ID)] = [ID]))
 
RETURN CALCULATE(
    DIVIDE((_group1 + _group2 + _group3), (0.3+0.25+0.45)), FILTER('Goal Table', [ID] = [ID]))
 
The issue with my current calculation is that it ignores the IF statement in my formulas and calculates the weighted average based on the VAR for each group. I don't think that it is an issue with my weighted average measure, as it calculates the weighted average accurately without the IF statement.
 
Thank you in advance! Also I apologize as I cannot share my pbix file, as it contains sensitive data.
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi NJ81858 ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Measure1 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 1', [Value]), FILTER('Metric 1', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 1]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Measure2 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 2', [Value]), FILTER('Metric 2', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 2]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Measure3 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 3', [Value]), FILTER('Metric 3', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 3]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Weighted Average = 
VAR _group1 = [Measure1]*0.3
VAR _group2 = [Measure2]*0.25
VAR _group3 = [Measure3]*0.45
 
RETURN DIVIDE((_group1 + _group2 + _group3), (0.3+0.25+0.45))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi NJ81858 ,

     

    What is the relationship between Global Table and Table1 and Table2, what do they do and what do they look like?

    Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

    Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NJ81858's avatar
      NJ81858
      Helper IV
      3 years ago

      v-jianboli-msft So my Goal Table has goals for each group for 3 different metrics, and are related to my other tables by the ID number.

       

      An example of my Goal Table looks like this:

      IDGoal Metric 1Goal Metric 2Goal Metric 2
      17104
      2589
      36311

       

      And then each of my metric tables look like this (We will call this Metric 1) :

      IDValue
      15
      210
      32
      16
      213
      31

       

      From there I calculate the percentage of the goal that each ID has achieved like this (note that I am doing this in typical math to display my issue instead of using my DAX code, for reference this is my Measure1 DAX formula) :

      ID 1 Metric 1 = 5+5= 10

      Goal for ID 1 Metric 1 = 7

      10/7 = 1.428 = 142.8%

       

      ID 2 Metric 1 = 10+13 = 23

      Goal for ID 1 Metric 1 = 5

      23/5 = 4.6 = 460% (This will max out at 150%)

       

      ID 1 Metric 1 = 2+1 = 3

      Goal for ID 1 Metric 1 = 6

      3/6 = 0.5 = 50%%

       

      Once I have those percentage of the goal values, I have to calculate a weighted average of the percentage of the goal reached, so for the sake of this example, I'm going to just show example values. It looks like this for ID 2:

       

      ID 2 Metric 1 = 150% (as this maxes out at 150%)

      ID 2 Metric 2 = 100%

      ID 2 Metric 3 = 35.8%

       

      From there I want to calculate the weighted average like this (The desired result of my Weighted Average DAX measure) :

       

      ( (1.5 * 0.3) + (1 * 0.25) + (0.358 * 0.45) ) / (0.3 + 0.25 + 0.45)

      0.8611 / 1 = 86.11% Weighted Average

       

      The issue with my formula is that it is calculating it like this, ignoring my condition that sets a max for the value (The result of my Weighted Average DAX measure currently) :

      ( (4.6 * 0.3) + (1 * 0.25) + (0.358 * 0.45) ) / (0.3 + 0.25 + 0.45)

      1.7911 / 1 = 179.11% Weighted Average

       

      In it's current state, my DAX measures work as they should, they just ignore the IF statement that sets the maximum percentage for one of the percentage of the goals and uses the 460% in my weighted average calculation instead of the 150% value.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi NJ81858 ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Measure1 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 1', [Value]), FILTER('Metric 1', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 1]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Measure2 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 2', [Value]), FILTER('Metric 2', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 2]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Measure3 = 
VAR _variable = CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('Metric 3', [Value]), FILTER('Metric 3', [ID] = [ID])),
        CALCULATE(
            SUMX('Goal Table', [Goal Metric 3]), FILTER('Goal Table', [ID] = [ID]))))

RETURN IF(_variable >= 1.5, 1.5, _variable)

Weighted Average = 
VAR _group1 = [Measure1]*0.3
VAR _group2 = [Measure2]*0.25
VAR _group3 = [Measure3]*0.45
 
RETURN DIVIDE((_group1 + _group2 + _group3), (0.3+0.25+0.45))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.