SUMPRODUCT IFS?

[mrothschild]

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 

,FILTER(SummaryInputTable,SummaryInputTable[Interest Rate]>0)

into the denominator CALCULATE, I get a really screwed up number.  Example Data and desired output is below:

WAVG Interest Rate =

VAR __CATEGORY_VALUES = VALUES('SummaryInputTable'[Asset ID])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(

                SUM('SummaryInputTable'[Interest Rate])

                    * SUM('SummaryInputTable'[Capital])

            )

        ),

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(SUM('SummaryInputTable'[Capital]))

        )

    )

Asset ID	Capital	Interest Rate
A	2200	9%
B	3330	5%
J	1600	0%
	Desired Output	6.59%
	Current Output	5.11%

[Anonymous · Accepted 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])
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[mrothschild]

Thanks so much - works perfectly!