Weighted Average Calculation in Power BI

Question

Hi all, any advice in relation to the following problem would be greatly appreciated.

Background to problem:

Data related to products is stored in a database. Data for two attributes is displayed on tiles – when a single product is selected it will display the attribute value for that product, when there is a multiple selection it needs to display a weighted average for that selection of products.

Each product has a different value of sales in a year, which is stored in local currency. The requirement is to show the weighted average by sales of that attribute. To do this I have converted the local currency values into a consistent currency e.g. GBP.

Where a value for an attribute is NULL, this should be excluded from the weighted average calculation. I am having difficulty calculating the weighted average.

Example:

In excel I have applied the logic for the weighted average calculations:

Question – how would you implement a complex calculation like this into Power BI, using DAX? The tile must show the attribute 1 value for any given product when only that is selected and then the tile must display the weighted average when multiple products are selected.

Thanks in advance

bcdobbs · Accepted Answer

Have a look the following two measures.Sum of Sales = SUM( Data[Sales] )Weighted Average 1 =

VAR AllSales = 
    CALCULATE (
        [Sum of Sales],
        REMOVEFILTERS()
    )

VAR Numerator =
    SUMX(
        Data,
        Data[Attribute 1] * DIVIDE( [Sum of Sales], AllSales )
    )

VAR Denominator =
    CALCULATE(
        SUMX(
            Data,
            DIVIDE( [Sum of Sales], AllSales )
        ),
    NOT ISBLANK(Data[Attribute 1])
    )

RETURN DIVIDE(Numerator, Denominator)

Forum Discussion

Weighted Average Calculation in Power BI

2 Replies

Related content

Calculating Weighted Average

Calculating Weighted Sales

Weighted Average DAX

calculating weighted average

weighted average calculation