Forum Discussion
Weighted Avg Aggregation in DAX
MA,
Create the following measures in your table and create a bar chart as shown in the following screenshot.
Sum of Pieces Sold = CALCULATE(SUMX(Table2,Table2[Pieces Sold]*Table2[USD/lb]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Sum of USD/lb = CALCULATE(SUM(Table2[Pieces Sold]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Average = [Sum of Pieces Sold]/[Sum of USD/lb]
Measure = IF(ISFILTERED(Table2[Type]),
SUM(Table2[USD/lb]),
[Average]
)
Regards,
Ignoring the subtypes, the solution would calculate as follows:
Value = (50*4.80 + 20*5.00 + 40*3.00)/(50 + 20 + 40) = ~4,18
MA,
Create the following measures in your table and create a bar chart as shown in the following screenshot.
Sum of Pieces Sold = CALCULATE(SUMX(Table2,Table2[Pieces Sold]*Table2[USD/lb]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Sum of USD/lb = CALCULATE(SUM(Table2[Pieces Sold]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))
Average = [Sum of Pieces Sold]/[Sum of USD/lb]
Measure = IF(ISFILTERED(Table2[Type]),
SUM(Table2[USD/lb]),
[Average]
)
Regards,
Thank you very much! This is exactly what I was looking for. However, I would call your first measure "Sum of USD/lb" and the second one "Sum of Pieces Sold". This makes more sense and is easier to understand for others who might stumble upon this thread.