Forum Discussion
WEIGHTED AVG
SUM([Net_Active_Balance])
not SUM('Analytics_SourceDat',[Net_Active_Balance])
perhaps?
Community ChampionNeed to see your data. You should just be able to do something like:
SUM([balance]*[int rate])/COUNT([balance])
Methinks I got it...
WAC = SUMx('Analytics_SourceDat',[Net_Active_Balance]*[INTEREST_RATE]) / SUMx('Analytics_SourceDat',[Net_Active_Balance])
leonardmurphySkilled Sharer
You're welcome.
You also don't need a SUMX for the divider since that's a straightforward SUM of the Net_Active_Balance.
(SUMX works row-by-row, so performs slower than a straightforward SUM)
Good point- appreciate the support!
Hi,
Thanks for responding , the fields include balance & rate. Tried WAC = SUM([Net_Active_Balance]*[INTEREST_RATE]) / SUM([Net_Active_Balance])
However, the syntax doesn't work to arrive at the weighted avg rate since I get this error:
The SUM function only accepts a column reference as an argument.
- leonardmurphy
In this situation, you want to use SUMX rather than SUM.
SUMX('Table',[Net_Active_Balance]*[INTEREST_RATE])
(replacing table with the name of your table)
The SUMX means that it will multiple the balance by the interest rate for each row and then sum the result. There's a great blog post here that breaks it down: http://www.powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/
Wow- just as you posted, i got it to work after some trial n error with some googling! Thank you.
