Forum Discussion
Weighted Average
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 ex...
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.