Total Sum from Other Table

[newbie1997]

Hello! So I wanted to add the all amounts of Ending Inventory per product from another table, the formula  I used seems to work okay but when I test it, it displays 0. I'm still new and check all possible solutions, but I'm still stuck. This is my formula:

=SUM(SELECT(Inventory[Ending Inventory],[Product Code]=[_THISROW].[Code]))

I also tried doing it in Virtual Column as App Formula, but still doesn't work. 

Thank you for the help!

[WillowMobileSys]

In your Summary table, it is showing 3 lines for Vodka - Absolut - 750ml.

I wonder if you might be confused about what [Product Code] and [Code] actually have as values in the data. 

If you are using [Code] as a Ref type column, it will store the Key value of the row from the referenced table.  BUT, Ref columns us the Label column (from the referenced table) for display purposes - and display purposes only.  

Consider the two tables below:

Inventory  Table                                       (Label)                                                  Your Other Table

Product Code	Category	Description	Qty		Key	Code (ref)
A	Vodka	Absolut - 750ml	30		1	A
B	Vodka	Absolut - 750ml	60		2	B
C	Vodka	Absolut - 750ml	30		3	C

Suppose that Description was set as the Label column in the AppSheet table.   When the rows from "Your Other Table" are SHOWN, the Code column - being a ref type column - will use the designated Label column as the value to display...in this case the value found in the Description column which is "Absolut - 750ml".

But when any functions use the Code column, the actual stored values are use - A, B, C.

You can change your expression to match on the Label like this:

=SUM(SELECT(Inventory[Ending Inventory],[Label Column Name]=[_THISROW].[Label Column Name]))

Then ALL your Vodka rows will show the summed value of 120.

[newbie1997]

Hello Willy! Thank you very much for this very detailed response. Very much appreciated, I apologize but I think I'm still misunderstanding it quite a bit. 

I tried switching up all column names, but got errors instead. 😞 

First I tried is this:

SUM(SELECT(Inventory[Ending Inventory],([Code]=[_THISROW].[Product Code])))

But it says: 

Error in expression '[_THISROW].[Product Code]':Unable to find column 'Product Code'

Second one:

SUM(SELECT(Inventory[Ending Inventory],([Code]=[_THISROW].[ID])))

It says again, Unable to find ID column

Very sorry for the inconvenience. Let me know what details you need.

[newbie1997]

But the way, I tried doing the SUMIFS formula in Google Sheets, hoping it will reflect in the app, but the app become laggy and the formula only works in Google Sheet but doesn't display in the app. 😞 

[ADanny]

I encountered this issue but realise if a column is of 'reference' type, then you'll need to amend your summation formula to [_THISROW].[Row ID].

So mine looks like
SUM(SELECT(AccountTransaction[Amount], [Account Name] = [_THISROW].[Row ID]))
instead of
SUM(SELECT(AccountTransaction[Amount], [Account Name] = [_THISROW].[Account Name]))