WEIGHTED AVG

[allmana]

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.

[rkalantri]

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]

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]

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

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

[allmana]

Methinks I got it...

WAC = SUMx('Analytics_SourceDat',[Net_Active_Balance]*[INTEREST_RATE]) / SUMx('Analytics_SourceDat',[Net_Active_Balance])

[leonardmurphy]

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)

[allmana]

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."

[leonardmurphy · Accepted solution]

SUM([Net_Active_Balance])

not SUM('Analytics_SourceDat',[Net_Active_Balance])

perhaps?

[allmana]

Spot on-  just realized the table name...

[allmana]

Good point-  appreciate the support! 

[allmana]

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/

[allmana]

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