Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |