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
allmana
Regular Visitor

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.

1 ACCEPTED SOLUTION

SUM([Net_Active_Balance])

 

not SUM('Analytics_SourceDat',[Net_Active_Balance])

 

perhaps?

---
In Wisconsin? Join the Madison Power BI User Group.

View solution in original post

12 REPLIES 12
rkalantri
Frequent Visitor

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.

rkalantri
Frequent Visitor

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.

Greg_Deckler
Super User
Super User

Need to see your data. You should just be able to do something like:

 

SUM([balance]*[int rate])/COUNT([balance])

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)

---
In Wisconsin? Join the Madison Power BI User Group.

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?

---
In Wisconsin? Join the Madison Power BI User Group.

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/

---
In Wisconsin? Join the Madison Power BI User Group.

Wow-  just as you posted, i got it to work after some trial n error with some googling!  Thank you.

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.