Forum Discussion

azale2407's avatar
azale2407
Frequent Visitor
3 years ago
Solved

Weighted Average Question

Updated: 3/2/2023 @ 250PM

Hi,

 

I have an issue I ran into with using a quick measure for weighted average.  I listed my formula below.  But basically realized my issue is that the "Value" formula only brings over unique values.  So when I have two entries that are both 95.  The weighted average should be 95, as they are both the same... but it gives me 190.  Is there a different forumula I should use other than Values to give me all values, not just unique?

 

Sample Table (all value do not change.  Once inputed they never change):

       UPB                  LTV

A    300,000            95.00

B    100,000            95.00

C    200,000            80.00

 

Note: I simply use slicers to filter out with loans I am analzying together.  So scorecard with this measure below shows weighted avgerage or is supposed to.  Does not work properly as discribed below. 

 

Current Formula:

LTV weighted by UPB per LTV =
VAR __CATEGORY_VALUES = VALUES('Tape Data'[LTV])
RETURN
    DIVIDE(
        SUMX(
            KEEPFILTERS(__CATEGORY_VALUES),
            CALCULATE(SUM('Sample Table'[LTV]) * SUM('Sample Table'[UPB]))
        ),
        SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('Sample Table'[UPB])))
    )
 
Obviously weighted average needs to give me these values but currently does not:
Weighted Avg of all 3 entries (A,B,C) = 90.00
Weighted Avg of A and B = 95.00
Weighted Avg of B and C = 85.00
Weighted Avg of A and C = 89.00
 
** What I get now for example when I do just weighted average of A and B = 180.00
** Works fine when I do B and C weighted average as those are not the same value in LTV = 85.00
 
Suggestions?
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    azale2407 , Try like

     

    Divide(SUMX('Sample Table','Sample Table'[LTV] * 'Sample Table'[UPB]), calculate( SUM('Sample Table'[UPB]), allselected()))

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    azale2407 , Try like

     

    Divide(SUMX('Sample Table','Sample Table'[LTV] * 'Sample Table'[UPB]), calculate( SUM('Sample Table'[UPB]), allselected()))