Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Weighted Average Calculation in Power BI

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 displ...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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)
bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

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)