Forum Discussion

allmana's avatar
allmana
Regular Visitor
10 years ago
Solved

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.
  • leonardmurphy's avatar
    leonardmurphy
    10 years ago

    SUM([Net_Active_Balance])

     

    not SUM('Analytics_SourceDat',[Net_Active_Balance])

     

    perhaps?

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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

 

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

 

 

allmana's avatar
allmana
Regular Visitor
10 years ago

Methinks I got it...

 

 

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

  • leonardmurphy's avatar
    leonardmurphy
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    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's avatar
      allmana
      Regular Visitor
      10 years ago

      Good point-  appreciate the support! 

    • allmana's avatar
      allmana
      Regular Visitor
      10 years ago

      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's avatar
        leonardmurphy
        Icon for Skilled Sharer rankSkilled Sharer
        10 years ago

        SUM([Net_Active_Balance])

         

        not SUM('Analytics_SourceDat',[Net_Active_Balance])

         

        perhaps?