Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |