Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |