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.

 

AlB's avatar
AlB
Community Champion
7 years ago

i_kafali

Try this:

1. Place a matrix visual in your report

2. Place Table1[Department] in the rows of the matrix

3. Place this measure in values of the matrix:

 

 

Measure =
DIVIDE (
    SUMX ( Table1, Table1[Volume (kg)] * Table1[Department Unit Sales Price] ),
    SUMX ( Table1, Table1[Volume (kg)] )
)