Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrothschild
Continued Contributor
Continued Contributor

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
 20082009201020112012201320142015201620172018
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

 

 

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.