Multiplying across tables

[sfink22]

I'm trying to create some matrix visuals in my dashboard for some product manufacturing information. 

I have three tables that are related in my model by my primary key, Recipe. The Recipe table has a 1:Many relationship with the Volume table, and a 1:Many relationship with the Component Mixes table, like this:

"Volume" * - 1 "Recipe" 1 - * "Component Mixes"

Each Recipe has 3 components, and the mix of components may be different between recipes. There are also 6 different production sites, and not all Recipes are made at each site. Here is an example of my current table setup:

Volume Table

Recipe	Volume	Site
1	100	A
1	200	B
1	150	C
1	500	D
1	50	E
2	50	B
2	50	D
2	100	E
2	200	F
3	50	A
3	25	C

Component Mixes Table

Recipe	Component	% Mix
1	AA	25%
1	BB	50%
1	CC	25%
2	DD	25%
2	BB	50%
2	CC	25%
3	EE	60%
3	FF	30%
3	CC	10%

I'm looking to multiply between two of those tables, "Component Mixes" and "Volume", which are linked together with the Recipe table between them. I want to create matrix  #1 that tells me the overall volume of each component by recipe, regardless of site. It should look like this:

Component	Recipe 1	Recipe 2	Recipe 3
AA	250	0	0
BB	500	200	0
CC	250	50	7.5
DD	0	100	0
EE	0	0	45
FF	0	0	22.5

Matrix #2 is similiar, but instead of having Recipes as the columns, it would have Sites (so the total volume of components produced at each site).

Component	Site A	Site B	Site C
AA (etc.)

I've been trying to use Related functions, which are returning some values, but the values aren't correct. Any help is appreciated!

[Jihwan_Kim]

Hi, @sfink22 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

https://www.dropbox.com/s/xucuedhs8p8g5bs/sfink22.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[sfink22]

Thank you so much! This appears to work perfectly for everything except for the Total column, where I am getting crazy huge numbers. Any idea why they aren't totaling across the rows properly?

[Jihwan_Kim · Accepted solution]

Hi, @sfink22 

Sorry that I missed to fix the total.

Please replace the measure to the below.

Result =
SUMX (
VALUES ( Recipe[Recipe] ),
CALCULATE (
SUMX ( Component, Component[% Mix] ) * SUMX ( Volume, Volume[Volume] )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[sfink22]

Thank you, this worked perfectly!