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 matte...
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]

     

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.

      • pcowman1's avatar
        pcowman1
        Helper I
        6 years ago

        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.