Matrix table with consolidating data

[mrothschild]

Link to excel data: https://www.dropbox.com/s/99u6ubvle36mlak/Matrix.xlsx?dl=0

I have a table with Units Sold, Year of Sale, sale prices for each of low, mid, and high wear & tear.  From that table, I'm trying to create the table shown below, which is the unit-weighted average by transaction year, for those cells on Table3 where there's a sale price and units.  

I'm pretty new to BI and have no earthly clue how to create SUMPRODUCTS like this in DAX even though I'm pretty familiar with them in Excel.  

Excel formula to calculate cell at "Low"/"2008" shown below  =SUMPRODUCT(--(Table3[Year]=G$3),--(Table3[low]<>"na"),Table3[Total Units],Table3[low])/SUMPRODUCT(--(Table3[Year]=G$3),--(Table3[low]<>"na"),Table3[Total Units])

G$3 = column header equating to 2008.

Desired output:

	Annual Transaction Price - Unit Weighted Universe
	Transaction Year
	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
Low	$624	$990	$914	$812	$926	$914	$893	$926	$846	$847	$762
Mid	$512	$841	$771	$685	$780	$779	$808	$819	$751	$739	$645
High	$454	$709	$670	$582	$645	$651	$699	$696	$628	$609	$404

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mrothschild]

Thank you so much!

I'll play around with this as there are additional filters I'm going to add so I can slice by, as an example, model type.  But this is fanstastic - thank you again.

[Ashish_Mathur]

You are welcome.