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 av...
  • 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]
    )
)
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]
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your prompt response, Jihwan. 

     

    - What if the first condition, i want to check for IS NOT NULL

    (not sure of Power BI syntax).

    - The second condition I would prefer = 1--then it will only be used if it's 1.

     

    Thank you.

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

      Hi,

      Please try the below, or please share your sample pbix file with how your desired outcome looks like, and then I can try to come up with a more relevant solution.

       

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

        Hi Jihwan - I really appreciate you working on this with me. Everything looks good and the DAX you helped with worked but in this case i do not want to use "Category" any longer. 

         

        Rather than using "Category", I want to use the "HAS Rating" indicator to essentially tell us the same thing. 

         

        --For the first condition, I want to check that the Rate Column isn't Null (not sure of power BI syntax). The data does not come out of the database as blank but as Null. 

        --While the other condition is to have HAS Rating = 1

         

        I hope i make sense. Thank you