Forum Discussion

unclejemima's avatar
unclejemima
Icon for Post Patron rankPost Patron
7 years ago

Help adding stock from one table with different types to another consolidated

Ok guys. Super appreciate the help. This forum rocks :-) Bear with me, I've got good screenshot and hopefully you can help me with this one :-)

 

Table: Inventry has my inventory records that are unique via the Part Number.

Table: Stok has the quantities broken up by the StockStaus.

 

The available stock, is all the Stock Status 1

The On Layaway is all the Stock Status 6

The On Workorder is all the Stock Status 5

The total Stock is Status 1, 5 and 6 added together

 

 

I'm trying to make 4 column's in the Inventry table that references that Stok table to give me proper inventory quantities.

 

To do so I'll need a formula that adds all the stock types based on the StockStatus, and then counts the PickQuantity for Available Stock, Layaway, Workorder and Total stock.

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi unclejemima,

     

     

    I'm assuming your both table are linked by the part number? 

     

    You don't need to create a new column just create the measure:

     

    Stock = SUM(STOK[PickQuantity])

    Then used it in your visuals, be aware that if you are making charts based on part number you need to use the table inventory part number (where you have the unique values).

     

    Regards,

    MFelix

    • unclejemima's avatar
      unclejemima
      Icon for Post Patron rankPost Patron
      7 years ago

      I think they are connected via a relationship if that's what you mean...

       

      But the part number in the STOK table matches the Part number in the INVENTRY table.

       

      I was hoping to do a column that I could see, perhaps via LOOKUPVALUE and then If statements.

       

      Would you be able to suggest how I could build the information with a column, not a measure?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        Hi unclejemima,

         

        Assuming that both your table are related, create a nem column on your inventory table with the following syntax:

         

        Inventory = CALCULATE(SUM(STOK[PickQuantity]); RELATEDTABLE(STOK))

         

        Regards,

        MFelix

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

    unclejemima,

     

    Table: Inventry has my inventory records that are unique via the Part Number.

    Table: Stok has the quantities broken up by the StockStaus.

     

    The available stock, is all the Stock Status 1

    The On Layaway is all the Stock Status 6

    The On Workorder is all the Stock Status 5

    The total Stock is Status 1, 5 and 6 added together



    Could you please show a sample data of the stock table and inventory table and give the expected results in your table? The screenshot you have posted seems couldn't be zoom in.

     

    Regards,

    Jimmy Tao