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]))

[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]))

[Ksenia1]

Thank you so much, @Suvrutt_Gurjar! The second option did the magic.

Though, I am very curious to find out how to solve it with the first option. Unfortunately, with your suggestion the price staid at 0.

[Suvrutt_Gurjar]

THank you for the update.

I believe the first option should also work with proper column names.

[Ksenia1]

Okay, let's try. So this is my rev ref column:

My Hardware table:

My Inventory table:

Hence, my formula should be:

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

It gives me green light but

[Suvrutt_Gurjar · Accepted solution]

Please try below approach

1. Please create a VC called say [Related Hardware IDs] with an expression [Inventor][Hardware ID] in the Users table.

2. Then the expression for the VC with sum of hardware prices can be 

SUM(SELECT(Hardware[Price], IN([Hardware ID], [Related Hardware IDs]))) 

[Ksenia1]

Thanks @Suvrutt_Gurjar, this works as well. So, I guess it is not possible to go without creating another VC?

Am now encouraged to create more VCs 🙂

[Suvrutt_Gurjar]

This approach of using  SUM(SELECT(Hardware[Price]......is generally sync time expensive, especially if there are large number of (1000s) of records in the Hardware table.

The intermediate VC  [Related Hardware IDs]   was required because testing showed that the earlier suggested approach of using  [Inventor][Hardware ID]  directly in the SUM(SELECT(Hardware[Price]..... expression computes a list of [Hardware ID] used in all records of the inventory table and not just the list related to Hardware Ids of a single user.

On the other hand, if one tests [Inventor][Hardware ID] separately,  the expression lists [Hardware ID] list of only the related user record. That is why requested to create the intermediate VC.

As a result, to avoid multiple VCs and a sync time expensive expression, the second approach suggested of pulling hardware price in the Inventory table with a dereference expression ( [Hardware ID].[Price])  is better one.

[Ksenia1]

Thank you for clarifying! 🙏