In a file I'm working on, I have a fact table with year, plant, machine, part number, and part quantity produced. In a separate table (not sure if it'd be considered fact or dimension table), I have a table with year, plant, machine, and machine maintenance cost. Basically, a machine can be in one or more sites in a given year and it will have a separate associated cost in each location. Each machine can also produce multiple parts, and any part can be produced on multiple machines in multiple locations. I would like to have a matrix table with part ID in the rows, plant in the columns, and a machine maintenance cost per quantity produced for the value. I've linked the two tables by a concatenated plant-machine-year column and the values are unique in the cost table. Please help with creating a measure for this value I'm looking for.
I called this thread "Creating a percent of a column subtotal" because I assume the best course is to create a column in the fact table that represents the portion of production of each row relative to the overall plant-machine-year combination, but this may not even be the best way to achieve what I'm looking for.
I can share a sample file I created to illustrate what I'm working on, but not sure how to upload it here.
Thanks in advance!
Yes, it would really help if you share the pbix
You cannot upload, you have to share the URL to the file hosted elsewhere. Dropbox, Onedrive... or just upload it to a site like tinyupload.com (no sign-up required).
Hi @jdriscol ,
Just change the direction of the relationship between the test table and the fact table to both. We can create a measure to get the result we need.
AVE = CALCULATE(SUM(TestCosts[Maint Cost]))/CALCULATE(SUM(FactTable[Quantity]))
Please check the pbix as attached.
Thank you for the response. Since the original post, I was able to figure out a solution to my problem. To your point, I did have to have a bi-directional relationship between the fact table and the cost dimension, but I also needed to create a calculated column in the fact table and two measures to get to what I needed. I can post the sample file with the analog of what I did to my actual project if you wanted to compare (the numbers I get in my table are slightly different). I'm still not sure how to attach files to these posts, though.