Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |