SUMPRODUCT IFS?

Question

I am using the following expression to get a SUMPRODUCT measure, which gives me the weighted average [Interest Rate] based on [Capital].&nbsp;&nbsp;&nbsp;In some cases, [Interest Rate] = 0 because there's no loan outstanding, but the expression then has too high a denominator.&nbsp; If I insert&nbsp;&nbsp;,FILTER(SummaryInputTable,SummaryInputTable[Interest Rate]&gt;0)&nbsp;into the denominator CALCULATE, I get a really screwed up number.&nbsp; Example Data and desired output is below:&nbsp;WAVG Interest Rate = VAR __CATEGORY_VALUES = VALUES('SummaryInputTable'[Asset ID])RETURN&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;KEEPFILTERS(__CATEGORY_VALUES),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM('SummaryInputTable'[Interest Rate])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* SUM('SummaryInputTable'[Capital])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;KEEPFILTERS(__CATEGORY_VALUES),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(SUM('SummaryInputTable'[Capital]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;Asset IDCapitalInterest RateA22009%B33305%J16000%&nbsp;&nbsp;&nbsp;&nbsp;Desired Output6.59%&nbsp;Current Output5.11%

anonymous · Accepted Answer

mrothschild&nbsp; - 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] &gt; 0)
return DIVIDE(
    SUMX(_desired_rows,[Interest Rate] * [Capital]),
    SUMX(_desired_rows,[Capital])
)
I hope this helps. If it does, please Mark as a solution.I also appreciate Kudos.
&nbsp;

Forum Discussion

SUMPRODUCT IFS?

2 Replies

Related content

SumProduct with IF Condition

If formula

SumProduct in measure

SUMPRODUCT

SumProduct Between two Table Values

Asset ID	Capital	Interest Rate
A	2200	9%
B	3330	5%
J	1600	0%

	Desired Output	6.59%
	Current Output	5.11%