Forum Discussion

pcowman1's avatar
pcowman1
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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"
)