Measure using columns from two tables (not directly related)

[Anonymous]

Hello all,

newbie here.

It looks like I bit off more than I can chew.

I have three tables. Item - list of all possible components with their specifications. Stock_item - available amount of components and Product_item - lists of components needed for assembling different products. Item has one to many relation to both Stock_item and Product_item (one beeing on Item). Sample data below.

I need to calculate how many products of selected type I can manufacture from available components.

So I need to divide available amount by necessary amount for every component needed for selected product and get their minimum.

Sample data:

Item	...
S4 - 001	...
S4 - 002	...
TX - 040	...
TX - 030	...
TX - 029	...
TX - 053	...
Z4 - 013	...
TX - 044	...
TX - 038	...
TX - 042	...
TX - 034	...
TX - 033	...
TX - 058	...
TX - 084	...
TX - 003	...
TX - 076	...
TX - 033	...
TX - 058	...
TX - 060	...
D4 - 005	...
D4 - 006	...
D4 - 007	...
TX - 048	...
TX - 050	...
TX - 051	...

StockItems	Amount
S4 - 001	450
S4 - 002	500
TX - 040	380
TX - 053	360
Z4 - 013	150
TX - 044	100
TX - 038	250
TX - 042	360
TX - 033	150
D4 - 005	100
TX - 084	250
TX - 076	175
TX - 030	320
TX - 058	360
TX - 060	150
D4 - 006	100
D4 - 007	250
TX - 048	100
TX - 050	250

ProductItems	Amount	Product
S4 - 001	5	A
S4 - 002	3	A
TX - 040	15	A
TX - 030	11	A
D4 - 006	2	A
D4 - 007	8	A
Z4 - 013	8	A
TX - 044	4	A
S4 - 001	8	B
TX - 084	3	B
TX - 040	15	B
TX - 033	11	B
TX - 058	8	B
TX - 084	6	B
TX - 003	4	B
TX - 076	10	C
TX - 033	5	C
TX - 058	8	C
TX - 060	8	C
D4 - 005	4	C
D4 - 006	8	C
D4 - 007	5	C
TX - 048	3	C
TX - 050	7	C
S4 - 001	8	C

[Anonymous]

@Greg_Deckler Wow. Thank you. I'm not entirely sure what I'm doing yet, but it seems to work as it should.

[Greg_Deckler · Accepted solution]

@Anonymous Perhaps a column in your last table like the following. You could use the MIN by product to get the number of product that can be produced.

Column = 
  VAR __Item = 'Table3'[ProductItems]
  VAR __Amount = MAXX(FILTER('Table2',[StockItems] = __Item),[Amount])
RETURN
  DIVIDE(__Amount, [Amount])