Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
6 years ago
Solved

SUMPRODUCT IFS?

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 lo...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    mrothschild
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

    Thanks so much - works perfectly!