Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am using the following expression to get a SUMPRODUCT measure, which gives me the weighted average [Interest Rate] based on [Capital].
In some cases, [Interest Rate] = 0 because there's no loan outstanding, but the expression then has too high a denominator. If I insert
Asset ID | Capital | Interest Rate |
A | 2200 | 9% |
B | 3330 | 5% |
J | 1600 | 0% |
Desired Output | 6.59% | |
Current Output | 5.11% |
Solved! Go to Solution.
@mrothschild - The following Measure should work. It first removes the 0-interest rows and then performs the weighted average.
Weighted =
var _desired_rows = FILTER(Rates, Rates[Interest Rate] > 0)
return DIVIDE(
SUMX(_desired_rows,[Interest Rate] * [Capital]),
SUMX(_desired_rows,[Capital])
)
@mrothschild - The following Measure should work. It first removes the 0-interest rows and then performs the weighted average.
Weighted =
var _desired_rows = FILTER(Rates, Rates[Interest Rate] > 0)
return DIVIDE(
SUMX(_desired_rows,[Interest Rate] * [Capital]),
SUMX(_desired_rows,[Capital])
)
Thanks so much - works perfectly!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |