Forum Discussion
Multiplying across tables
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!
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.
4 Replies
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.
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?
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.
