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

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