Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sfink22
Helper I
Helper I

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

RecipeVolumeSite
1100A
1200B
1150C
1500D
150E
250B
250D
2100E
2200F
350A
325C

 

Component Mixes Table

RecipeComponent% Mix
1AA25%
1BB50%
1CC25%
2DD25%
2BB50%
2CC25%
3EE60%
3FF30%
3CC10%

 

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:

 

ComponentRecipe 1Recipe 2Recipe 3
AA25000
BB5002000
CC250507.5
DD01000
EE0045
FF0022.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).

 

ComponentSite ASite BSite 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!

1 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.

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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @sfink22 

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

 

Picture1.pngPicture2.png

 

 

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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Thank you, this worked perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.