Forum Discussion
Creating BOM Availability
Hello,
Create these calculated columns:
Inventory Per Category Per Item = CALCULATE ( SUM ( 'Table'[Inventory] ), ALLEXCEPT ( 'Table', 'Table'[Common Item Number], 'Table'[Item_Category_Code] ) )Min Inventory in All Categories Per Item = CALCULATE ( MIN ( 'Table'[Inventory Per Category] ), ALLEXCEPT ( 'Table', 'Table'[Common Item Number] ) )BOM = //returns true if [Inventory Per Category] = [Min Inventory in All Categories Per Item] 'Table'[Inventory Per Category] = 'Table'[Min Inventory in All Categories Per Item]
Hi pcowman1
Assuming that whether an item can be done is based on sum of shortage per unique Common Item Numer and Part Number, create a calculated column similar to below:
Item Part Can Be Done =
IF (
CALCULATE (
SUM ( Table[Shortage] ),
ALLEXCEPT ( Table, 'Table'[Part Number], 'Table'[Common Item Number] )
) > 0,
"No",
"Yes"
)Or if it is based solely on Common Item Number:
Item Can Be Done =
IF (
CALCULATE (
SUM ( Table[Shortage] ),
ALLEXCEPT ( Table, 'Table'[Common Item Number] )
) > 0,
"No",
"Yes"
)
I'm trying for the second - I got No on both
Can you please post a sample data (not an image) and then your exact conditions/criteria (what can be made and based on what)? Excel formula coud also be helpful.
I'm seriously considering moving the whole thing to excel because I need to pivot a table and join it to another table. Might easier in excel where I can do vlookup on the columns but I'm really not that great with vlookup in excel either. I need to up my game in this area.
I fixed the first one by doing a second table that uses the common number and bom number and then does a calculated column filtering.
Say I'm making these two parts - Item 1 has a bottle neck at Category 1 because there are only 2.
Item 2 has a bottle neck at Category 2 because there are only 20 and the rest of the categories are larger.
Common Item Number Item_Category_Code Part Number Inventory Item 1 Category 1 Part 1 2 Item 1 Category 2 Part 2 5 Item 1 Category 3 Part 3 15 Item 1 Category 3 Part 4 31 Item 1 Category 3 Part 5 59 Item 1 Category 5 Part 6 63 Item 1 Category 6 Part 7 168 Item 1 Category 4 Part 8 254 Item 1 Category 3 Part 9 398 Item 1 Category 3 Part 10 647 Item 1 Category 5 Part 11 725 Item 1 Category 3 Part 12 2000 Item 2 Category 1 Part 13 50 Item 2 Category 2 Part 14 20 Item 2 Category 4 Part 15 8 Item 2 Category 3 Part 16 15 Item 2 Category 4 Part 17 25 Item 2 Category 3 Part 18 31 Item 2 Category 5 Part 19 63 Item 2 Category 4 Part 20 127 Item 2 Category 6 Part 21 168 Item 2 Category 3 Part 22 398 Item 2 Category 3 Part 23 647 Item 2 Category 5 Part 24 725 Item 2 Category 3 Part 25 2000 So the end result would look like this:
Common Item Number Item_Category_Code Inventory Item 1 Category 1 2 Item 2 Category 2 20 I'm going to add to this (and I really think this should be simple and don't know why I'm having a hard time):
if (sum(Inventory in Category) = min(sum(inventory in Category of all category sums))
Is that clearer? Am i making it worse?