Forum Discussion
Solved
Creating BOM Availability
I'm trying to make a report for whether you can make a master part number or not, and then which sub-assemblies can be used. I'm connecting to a Business Central DB but that part doesn't really matte...
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]
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?
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]Thank you! It's late here and I've been working since early but it looks perfect! Many thanks. If I ever see you, I owe you a drink.