Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Weighted Averages

Hi everyone,

 

I'm trying to come up with a table in a report that has product line in the rows and weighted average product cost as the value. I have a dimension table with unique product number, product line (product lines are composed of many product numbers), and product cost, and a fact table (linked by unique product number) with unique product number and sales quantities. I want the weight of the weighted average to be set by sales quantities. Any help with the DAX formula would be much appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi, I can't really upload the file because of proprietary data, but I figured out the code on my own. Basically, the DAX reads as follows:

    WtdAvgCost = DIVIDE(
    SUMX(SalesTable, SalesTable[SalesQuantity] * RELATED(ProductTable[ProductCost])), 
    SUMX(SalesTable, SalesTable[SalesQuantity]),
    0)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, I can't really upload the file because of proprietary data, but I figured out the code on my own. Basically, the DAX reads as follows:

    WtdAvgCost = DIVIDE(
    SUMX(SalesTable, SalesTable[SalesQuantity] * RELATED(ProductTable[ProductCost])), 
    SUMX(SalesTable, SalesTable[SalesQuantity]),
    0)