Creating BOM Availability

[pcowman1]

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 matter when you get to PBI.

Here's a sample of what the data looks like:

Part 1 can't be made because it has shortage in inventory 

Item 2 can be made but has a bottle neck on Category 4.

The biggest part is - What is available to be made or not made.

Can this be done with a measure/filter? I'm not good with filters yet.

[danextian]

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"
)

[pcowman1]

I'm trying for the second  - I got No on both

[danextian]

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.

[pcowman1]

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

[pcowman1]

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?

[danextian · Accepted solution]

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]

[pcowman1]

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.

[pcowman1]

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.