Forum Discussion

pcowman1's avatar
pcowman1
Helper I
6 years ago
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 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.

Need Help
  • danextian's avatar
    danextian
    6 years ago

    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]

     

8 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    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's avatar
      pcowman1
      Helper I
      6 years ago

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

      • danextian's avatar
        danextian
        Super User
        6 years ago

        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.