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. Therefore i can compare product's average price versus departments average.

 

What kind of Dax formula should i make?

 

Thanks,

 

 

DepartmentProductVolume (kg)Unit Sales PriceDepartment Unit Sales Price
AX1101,11,19
AX2201,21,19
AX1401,21,19
BX2501,31,31
BX4201,41,31
BX3151,21,31
CX1251,51,37
CX5301,21,37
CX4501,41,37
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

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi i_kafali

    What is the exact calculation you want to perform to compute the average? Average per product and department taking the volume into account? Can you provide a  clear illustrative example?

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