Forum Discussion

Oomsen's avatar
Oomsen
Icon for Helper III rankHelper III
7 years ago

When can i deliver?

My sales collegeas would like to know when we can deliver a potential order.

Let's say we got an order of 8 x Robot A.

To build Robot A we have a multilevel BOM. 

In the BOM is set which quantity of Part 1, Part 2 and Part 3 is necessary to make 1 x Robot A.

In the BOM is also set which quantity of Part ABC is necessary to make 1 x Part 1.

Every Part has his own lead time. 

I would like to build a report in which i can set Robot A x 8 and then get a view of maximum lead time and necessary quantity. 

Currently i have 2 tables. Items [Item code] and BOM [partitemcode].

It looks like below:

Item code     Partitemcode      Quantity

Part 1            Part ABC             3

                     Part DEF              1

                      Part GHI              2

Part 2             Part JKL              4

 

 

 

Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Oomsen 

    Assume you have tables as below

    Item table

    item code

    part1

    part2

    part3

     

     

    Bom table

    item code

    partitemcode

    quantity

    robot

    quantity of item

    time/part/quantity

    part1

    part abc

    3

    robot a

    1

    1

    part1

    part def

    1

    robot a

    1

    2

    part1

    part ghi

    2

    robot a

    1

    3

    part2

    part jkl

    4

    robot a

    2

    1

    part3

    part nmp

    2

    robot a

    2

    2

    part1

    part abc

    2

    robot b

    1

    1

    part1

    part def

    3

    robot b

    1

    2

    part2

    part jkl

    1

    robot b

    2

    1

    part3

    part nmp

    2

    robot b

    3

    2

     

    If my assumption is not correct, please show me an simple example dataset.

     

    Best Regards
    Maggie

    • Oomsen's avatar
      Oomsen
      Icon for Helper III rankHelper III
      7 years ago

      v-juanli-msft 

      The column robot isn't set in my bom table. This is because there are common parts between the robots.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Oomsen 

    Assume you have a table as below

    "components one robot" table

    item code partitemcode quantity of part quantity of item time/part/quantity
    part1 part abc 3 1 1
    part1 part def 1 1 2
    part1 part ghi 2 1 3
    part2 part jkl 4 2 1
    part3 part nmp 2 1 2

     

    I create columns in this table

    time per part = [quantity of part]*[time/part/quantity]

time per item = CALCULATE(SUM('components one robot'[time per part]),ALLEXCEPT('components one robot','components one robot'[item code]))

    Then i create another table

    time per item =
CALCULATE (
    SUM ( 'components one robot'[time per part] ),
    ALLEXCEPT ( 'components one robot', 'components one robot'[item code] )
)

    Add columns in this table

    lead time one robot = [quantity of item]*[time per item]

    add measures

    lead time of 8 robot = SUM('lead time one robot'[lead time one robot])*8

quantity of items for 8 robots = SUM('lead time one robot'[quantity of item])*8

total quantity of parts for 8 robots = SUM('lead time one robot'[total quantity of parts])*8

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.