Math operations on two columns of another sheet using SELECT

[Thomas_McIntosh]

I want to multiple values from two columns on another sheet based on a Select  ID

I found this post that shows exactly how to do what I want, but the syntax doesn't work. 

SUM(SELECT(generic_table_name[column_a*column_b];[id_column]=[_thisrow].[id_column]))

I tried both of these syntaxes and neither work:

SUM(SELECT(Purchases[Price*Quantity],AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([Price]),[Price] > 0))
)

SUM(SELECT((Purchases[Price]*Purchases[Quantity]),AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([Price]),[Price] > 0))
)

I realize that the operation can be performed in a Virtual Column on the referenced table, but for readability I would prefer to do the whole calculation in one formula.

Many thanks 🙂

[OptimiX_XcrY]

Inside [ ] you can only have A column name, not two or more.

Try to rewrite your formula as 

Select( column[A],...) * Select( column[B],...)

Instead of select(column[A*B],...) wich will never work

[OptimiX_XcrY]

SUM(

SELECT(Purchases[Price],AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([Price]),[Price] > 0))

*

SELECT(Purchases[Quantity],AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([quantity]),[quantity] > 0))

)

[Thomas_McIntosh]

Thanks for the response @OptimiX_XcrY 

For this data both SELECT statements have to have the same conditionals otherwise the lists returned may be of different lengths. 

SUM(

SELECT(Purchases[Price],AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([Price]),[Price] > 0))

*

SELECT(Purchases[Quantity],AND(([Product Barcode] = [_ThisRow].[Product Barcode]),ISNOTBLANK([Price]),[Price] > 0))

)

However, even when both lists are the same length, the multiplication returns zeros. Preview output : 

[TeeSee1]

Multiplying two lists (results of SELECT statements) do not work.

You have to have a real or VC column in the Purchase table with an expression [Price] * [Quantity] and SELECT this column over the same condition you have and then SUM the result.

[Thomas_McIntosh]

Thanks @TeeSee1 

I had already found that solution, but I regard it as a work-around in terms of the readability of the 'code'. It seems like a strange omission to me that there isn't a way to multiply the contents of lists.