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

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.