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?
