Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
If this post helps, then please consider accepting 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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you, this worked perfectly!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |