Having Trouble calculating inventory in and out on a table

[AGairAdmin]

I have a table that contains product inventory transactions. and I'm having trouble getting the expression right, I've watched all the app sheet training videos and webiars on it, but i just can't get it to function, it doesn't split out the product names and a sum total for each, ti just adds up everything of that transfer type no matter if the product is the same or not. 

I'm trying to calculate the totals for each chemical for each "Transfer Type", then Subtract the outgoing "transfer type"s from the incoming "transfer types"s and display them with the date range selectable. 

kinda like a report or audit of the inventory transactions 
see attached screen captures. 

 any help would be greatly approached. 

[Joseph_Seddik]

You should build two slices, one for each transfer type, incomingSlice and outgoingSlice. Their respective row filter conditions would be:

[Transfer Type] = "Incoming", and [Transfer Type] = "Outgoing". 

Your expression then would be:

SUM( SELECT(incomingSlice[Quantity], [productID] = [_ThisRow].[productID]) )
-
SUM( SELECT(outgoingSlice[Quantity], [productID] = [_ThisRow].[productID]) )

[ChristianLi]

To add on to this, the expression should be in a Virtual Column in Unique Inventory table since that data is dynamic (changes, not stored as a value).

[AGairAdmin]

@ChristianLi how would i get the data copied over the to the unique table or would I tie the unique table to the transaction list and then make the virtual columns?

[ChristianLi]

Hello @AGairAdmin ,

1. You can use REF as data type for the "Product Name" data ref to a unique table inventory.

2. Then  adding REF_ROWS to the unique table table (this are virtual columns too which can be automatically generated if you check "is part of" to the reference table of unique items)

3. Make another virtual Column for Unique Item Inventory name it "Available Quantity" with the following expression

sum(select([Related Transfer Product][Quantity],[Transfer type] = "Incoming"))

-sum(select([Related Transfer Product][Quantity],[Transfer type] = "Outgoing"))

[AGairAdmin]

i have made the slices , and they are displaying the products, but the products are staying in their groups and not sum up , like 2 product names with separate but correct qty @Joseph_Seddik 

[Joseph_Seddik]

You just have to put the SUM expression above where you want the sum to be displayed. For example if you have a Products table, where each row represents a unique product, in this table you should add a virtual column, name it for example: "Available Quantity", type Number and put the SUM expression above in its App Formula.