Forum Discussion
Matrix table with consolidating data
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 |
3 Replies
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.
You are welcome.
