SUM of values from another table, which is not directly referenced

[Ksenia1]

Hello everyone,

I need some help with an expression.

I have three tables:

1. User
2. Hardware 
3. Inventory, which has a reference to both User and Hardware

In a virtual column in the User table I would like to display the sum of all hardware prices, which are connected to the User in the inventory table. 

I have tried it with this Formula, though, the price stays at 0

SUM(SELECT(Hardware[Preis], IN(Hardware[Hardware ID], [Inventar][Hardware ID])))

Thanks in advance!

[Suvrutt_Gurjar · Accepted solution]

Please try an expression something like 

SUM(SELECT(Hardware[Preis], IN([Hardware ID], [Related Inventars][Hardware ID])))

Where [Related Inventars] is a rev ref column in the Users table and [Hardware ID] is the ref column in the Inventory table.

The above expression will be in general sync time expensive. 

Alternatively you could get  the price in the Inventory table with an expression something like 

[Ref column to Hardware Table in Inventory Table].[Price] in a column called say, [Hardware Price]

Where [Price] is the price column in the Hardware table.

Then in the user table the price sum can be 

SUM(SELECT([Related Inventars][Hardware Price]))