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.
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 |
Solved! Go to 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])
@Greg_Deckler Wow. Thank you. I'm not entirely sure what I'm doing yet, but it seems to work as it should.
@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])
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |