Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrothschild
Continued Contributor
Continued Contributor

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 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 IDCapitalInterest Rate
A22009%
B33305%
J16000%
   
 Desired Output6.59%
 Current Output5.11%
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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.

 

Thanks so much - works perfectly!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.