Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX: Weighted Average Prices From Two Tables

Hi all,

 

I'd like to calculate the Weighted Average Retail Price which was derived from the 'Sales' and 'Price' tables.  My data model is like: 

  • a 'Product' table and a 'Date' table which contains one to many relationship with the 'Sales' and 'Price' Tables
  • Weighted Average Retail Price = Summation of ('Sales'[Total Unit Sold] * 'Price'[Retail Price]) / Sum('Sales'[Total Unit Sold])

 

Product Table
- ProductID

 

Date Table
- Date

 

 

Sales Table

- ProductID

- Date

- Unit Sold

 

Price Table

- ProductID

- Price Scrapped Date

- Retail Price

 

Can anyone help with this?  Thanks in advance.

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    Anonymous 

     

    You may refer to the measure below.

    Measure =
DIVIDE (
    SUMX ( 'Table', 'Table'[Qty Sold] * 'Table'[Retail Price] ),
    SUM ( 'Table'[Qty Sold] )
)

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , try Like

     

    sumx(summarize('Product Table','Product Table'[ProductID],"_sum",sum('Sales'[Total Unit Sold]), "_avg",average('Price'[Retail Price])),divide(([_sum]*[_avg]),[_sum]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amitchandak, I tried the formula but it only returns an Average instead of Weighted Average :(... 

      Could you please help look into it?

       

      Here's one example of Week 13.

      • Weighted Average Retail Price = $168.32 <-- this is the desired result for Week 13
        • In Excel formula = SUMPRODUCT(Retail Price, Qty Sold) / SUM(Qty Sold)
      • Average Retail Price = $171.70

       

      WeekDateRetail PriceQty Sold

      3/24/2019

      		$162.990
      3/25/2019$162.991
      3/26/2019$162.992
      3/27/2019$162.992
      3/28/2019$159.990
      3/29/2019$194.991
      3/30/2019$194.990

       

      Thanks again for the help.

       

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Anonymous 

         

        You may refer to the measure below.

        Measure =
DIVIDE (
    SUMX ( 'Table', 'Table'[Qty Sold] * 'Table'[Retail Price] ),
    SUM ( 'Table'[Qty Sold] )
)