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

[unclejemima]

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.

[v-yuta-msft]

@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

---

[MFelix]

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]

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]

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