Forum Discussion

i_kafali's avatar
i_kafali
Helper II
7 years ago
Solved

Weighted Average for only one column

  Dear all,   I have below fields Department and Product and numbers for volume and sales price   What i want to add is another measure which calculates only departments average unit sales price...
Need Help
Show and Tell
Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi i_kafali

     

    You may try to create below measures:

    Volume*Unit Sales Price =
CALCULATE ( SUM ( Table1[Volume (kg)] ) * SUM ( Table1[Unit Sales Price] ) )

    Measure =
SUMX ( ALLEXCEPT ( Table1, Table1[Department] ), [Volume*Unit Sales Price] )
    / CALCULATE (
        SUM ( Table1[Volume (kg)] ),
        ALLEXCEPT ( Table1, Table1[Department] )
    )

     

    Regards,

    Cherie

i_kafali's avatar
i_kafali
Helper II
7 years ago

Below is the excel formula i want to perform in power bi

 

I have many fields in my dataset but in my calculation i want to calculate weighted average unit sales price of department so in the visual report i can show which products are below department's average which are above.

 

Using ALL or ALLEXCEPT dax formulas does not work since i have many other fields which induces a wrong result.

 

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi i_kafali

 

You may try to create below measures:

Volume*Unit Sales Price =
CALCULATE ( SUM ( Table1[Volume (kg)] ) * SUM ( Table1[Unit Sales Price] ) )

Measure =
SUMX ( ALLEXCEPT ( Table1, Table1[Department] ), [Volume*Unit Sales Price] )
    / CALCULATE (
        SUM ( Table1[Volume (kg)] ),
        ALLEXCEPT ( Table1, Table1[Department] )
    )

 

Regards,

Cherie