DAX calculation for weighted average

[RilwanFlame]

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 · Accepted solution]

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]
    )
)

[RilwanFlame]

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]

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]
    )
)

[RilwanFlame]

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]

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]
    )
)

[RilwanFlame]

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

[Jihwan_Kim]

Hi,

Please try to include your expected outcome as well.

Please try the below.

Only Bond weight avg: =
DIVIDE (
    SUMX (
        FILTER (
            Data,
            Data[HAS Rating] = 1
        ),
        Data[Balance] * Data[Rate]
    ),
    SUMX (
        FILTER (
            Data,
            Data[HAS Rating] = 1
        ),
        Data[Balance]
    )
)

[RilwanFlame]

Hi Jihwan -  The output is same as previous but with this new formula,
Can you have two conditions in the FILTER? I need to 1) make sure that Has Rating = 1, and 2) make sure that Rate is not NULL.

[Jihwan_Kim]

Hi,

new measure: =
DIVIDE (
    SUMX (
        FILTER ( Data, Data[HAS Rating] = 1 && Data[Rate] <> BLANK () ),
        Data[Balance] * Data[Rate]
    ),
    SUMX (
        FILTER ( Data, Data[HAS Rating] = 1 && Data[Rate] <> BLANK () ),
        Data[Balance]
    )
)

[RilwanFlame]

Hi Jihwan- 

Im back again concerning the weighted AVG.  This is basically where i pick the fields needed from two tables. 

My error is coming from the "Sheet1" table where it flags the field with red line. On looking up the meaning of the error : "A single value of column 'Market_Price' in table 'Sheet1' cannot be determined. This can happenwhen measure formula refers to a column that contains many values without specifyingan aggregation such as min, max, count, or sum to get a single result". 

Below is my formula (following what you helped with) but not sure what I am missing. 

Weighted Avg =
DIVIDE ( SUMX ( FILTER ( Sheet2, Sheet2[HAS_Analytics] = 1 && Sheet1[Market_Price] <> BLANK () ),Sheet1[Market_Value] * Sheet1[Market_Price] ),
SUMX ( FILTER ( Sheet2, Sheet2[HAS_Analytics] = 1 && Sheet1[Market_Price] <> BLANK () ), Sheet1[Market_Value] ) )

Thank you. 

[RilwanFlame]

The Column[Rate] will always be numeric, usually 2 decimal places. I think this is the right approach--SUMX with FILTER (two conditions), for both the numerator and denominator. I think that's what I need.

I really appreciate you, Jihwan. Thank you so much

[RilwanFlame]

Hi - Very much appreciated. Thank you so much