Weighted Average

[NJ81858]

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.

[v-jianboli-msft · Accepted solution]

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.

[v-jianboli-msft]

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]

@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:

ID	Goal Metric 1	Goal Metric 2	Goal Metric 2
1	7	10	4
2	5	8	9
3	6	3	11

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

ID	Value
1	5
2	10
3	2
1	6
2	13
3	1

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.