DAX: Weighted Average Prices From Two Tables

[Anonymous]

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.

[amitchandak]

@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]

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

WeekDate	Retail Price	Qty Sold
3/24/2019	$162.99	0
3/25/2019	$162.99	1
3/26/2019	$162.99	2
3/27/2019	$162.99	2
3/28/2019	$159.99	0
3/29/2019	$194.99	1
3/30/2019	$194.99	0

Thanks again for the help.

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may refer to the measure below.

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