Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
SUM([Net_Active_Balance])
not SUM('Analytics_SourceDat',[Net_Active_Balance])
perhaps?
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.
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])
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)
Just modified to SUM, but got error msg- is this a syntax error in formula?
"Too many arguments were passed to the SUM function. The maximum argument count for the function is 1."
SUM([Net_Active_Balance])
not SUM('Analytics_SourceDat',[Net_Active_Balance])
perhaps?
Spot on- just realized the table name...
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.
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.