Forum Discussion
WEIGHTED AVG
Hi, I am having difficulty created a weighted avg measure (balance*int rate) and tried creating a helper column to no avail. I am new to PBI and would appreciate assistance.
SUM([Net_Active_Balance])
not SUM('Analytics_SourceDat',[Net_Active_Balance])
perhaps?
12 Replies
- Greg_Deckler
Community Champion
Need 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)
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/
It doesnt work for me
Measure = SUM(SODUMP100915[NETAMT]-SUM(SODUMP100915[FREIGHTBOOKED]/SUM(SODUMP100915[NET_QUINTAL])))
The SUM function only accepts a column reference as an argument.
even tried creating a new column
Column = sum(SODUMP100915[NETAMT]-sum(SODUMP100915[FREIGHTBOOKED]/sum(SODUMP100915[NET_QUINTAL])))
The SUM function only accepts a column reference as an argument.
Please give me a solution so i can create weighted average.
It doesnt work for me
Measure = SUM(SODUMP100915[NETAMT]-SUM(SODUMP100915[FREIGHTBOOKED]/SUM(SODUMP100915[NET_QUINTAL])))
The SUM function only accepts a column reference as an argument.
even tried creating a new column
Column = sum(SODUMP100915[NETAMT]-sum(SODUMP100915[FREIGHTBOOKED]/sum(SODUMP100915[NET_QUINTAL])))
The SUM function only accepts a column reference as an argument.
Please give me a solution so i can create weighted average.
