Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX calculation for weighted average

Hi All - 

 

Hoping to find DAX help in calculating Weighted Average in power BI. 

In excel, I had my calculation as : Calculation for "Bond 1 and Bond 2"

 

1- How do I write that weight average in DAX?

2- In the case of having Several rows of Balance value but blank Ratio, how can it be calculated where the row of Balance value with blank Ratio can be omitted (Stock)? 

3- How can I write the DAX where if i do not want the "String", I can omit that row and have the other calculations for Bond done. 

 

How can i put all the DAX formular in one. 

 

Thank you. 

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file whether it suits your requirement.

     

     

    Only Bond weight avg: =
DIVIDE (
    SUMX (
        FILTER ( Data, NOT ( Data[Category] IN { "String", "Stock" } ) ),
        Data[Balance] * Data[Rate]
    ),
    SUMX (
        FILTER ( Data, NOT ( Data[Category] IN { "String", "Stock" } ) ),
        Data[Balance]
    )
)

12 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file whether it suits your requirement.

     

     

    Only Bond weight avg: =
DIVIDE (
    SUMX (
        FILTER ( Data, NOT ( Data[Category] IN { "String", "Stock" } ) ),
        Data[Balance] * Data[Rate]
    ),
    SUMX (
        FILTER ( Data, NOT ( Data[Category] IN { "String", "Stock" } ) ),
        Data[Balance]
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi - Very much appreciated. Thank you so much 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Jihwan- 

      Sorry to bother you but i have another question. 

      With the formula you helped with, what if i also have to Consider the HAS Rating = 1 and omit that of 0, even though there is value under Rate and Balance. 

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your message. Could you please try something like below whether it suits your requirement?

         

        Only Bond weight avg: =
DIVIDE (
    SUMX (
        FILTER (
            Data,
            NOT ( Data[Category] IN { "String", "Stock" } )
                && Data[HAS Rating] <> 0
        ),
        Data[Balance] * Data[Rate]
    ),
    SUMX (
        FILTER (
            Data,
            NOT ( Data[Category] IN { "String", "Stock" } )
                && Data[HAS Rating] <> 0
        ),
        Data[Balance]
    )
)