Weighted Average

[PC2022]

Good day,

I need to calculate weighted avg for PayItemNo and need help with dax for it.

"Average vs low" number represents a number i calculated based on submitted bids. In the table below (i exported it into excel from query editor) there are multiple instances of the same "pay item". First, they need to be grouped and then calculate weighted avg: total qty of "pay item" * sum of "average vs low" / total qty of "pay items". Thank you.

[PC2022]

Thank you!

[mohgh]

WeightedAvg =
DIVIDE(
SUMX(
VALUES('YourTable'[PayItemNo]),
SUMX(
FILTER('YourTable', 'YourTable'[PayItemNo] = EARLIER('YourTable'[PayItemNo])),
'YourTable'[Qty] * 'YourTable'[AverageVsLow]
)
),
SUMX(
VALUES('YourTable'[PayItemNo]),
SUMX(
FILTER('YourTable', 'YourTable'[PayItemNo] = EARLIER('YourTable'[PayItemNo])),
'YourTable'[Qty]
)
)
)

This formula uses the SUMX function to iterate over the unique values of 'PayItemNo', then within each iteration, it calculates the sum of the product of 'Qty' and 'AverageVsLow', and divides it by the sum of 'Qty' for that 'PayItemNo'. Finally, it divides the overall sum by the overall sum of 'Qty' for all 'PayItemNo' values.