I currently have four tables in a star schema with "Recipe" table as my link between all three tables. All relationships are 1:Many.
"Recipe" table:
I have a "Component Mix" table that shows the component breakdown of each recipe and the percentage for each component. i.e. Recipe #1 is 50% A, 25% B, 25% C, Recipe #2 is 60% A, 30% D, 10% E, etc.
"Component Mix" table:
My "Volume" table has a list of recipes with their corresponding volume. I created a measure that takes the volume number from the "Volume" table and mulitplies it by the component percentage from a "Component Mix" table. This gives me volume by component.
"Volume" table (also includes the Component Volume measure):
My last table is "Rates". It has the production rate for each component by production site. I need to get the Component Volume and divide it by its corresponding Rate, which will give me time to produce the given volume. Since some components are shared between recipes, I will also need it to aggregate time to produce for every component by site.
"Rates" table:
What I'd like to end up with:
| Component | Site 1 | Site 2 |
| A | [Site 1's rate for Component A * Site 1's volume of Component A] | [Site 2's rate for Component A * Site 2's volume of Component A] |
| B | [Site 1's rate for Component B * Site 1's volume of Component B] | [Site 2's rate for Component B * Site 2's volume of Component B] |
| C | etc. | |
Is this possible?
