Forum Discussion

PC2022's avatar
PC2022
Helper III
2 years ago

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 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.

 

 

 

2 Replies

  • mohgh's avatar
    mohgh
    Regular Visitor
    2 years ago

    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.