Dividing across tables with a measure

[sfink22]

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:

Recipe

Recipe Code

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:

Recipe

Component

% Mix

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):

Recipe

Volume

Site

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:

Recipe

Component

Site

Rate

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?

[v-deddai1-msft]

Hi @sfink22 ,

Would you please show us some sample data or sample pbix ? Then we can help you more accurately.

Best Regards,

Dedmon Dai

[lbendlin]

This sounds like the exact opposite of a good data model 🙂  Ideally multiple dimensions point to a single fact table.  In your case a single dimension seems to point to multiple facts.  That makes computations between the facts tricky as they have to go through the dimension, which can introduce ambiguity.

Think about your data some more - maybe you can come up with different relationships?