Weighted Average for only one column

[i_kafali]

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,

Department	Product	Volume (kg)	Unit Sales Price	Department Unit Sales Price
A	X1	10	1,1	1,19
A	X2	20	1,2	1,19
A	X1	40	1,2	1,19
B	X2	50	1,3	1,31
B	X4	20	1,4	1,31
B	X3	15	1,2	1,31
C	X1	25	1,5	1,37
C	X5	30	1,2	1,37
C	X4	50	1,4	1,37

[AlB]

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]

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 · Accepted solution]

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]

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