Calculated Column from two tables related to one another with a 3'rd table

[Anonymous]

Hello,

I have 3 tables: Query1 at Product and Invoice Level; Stock_KLG at Product Level; and DimProduct at Product Level

Query1 has an old Product code and Stock_KLG a new Product code. They are connected to one another by a mapping table DimProducts.

I want to create a calculated column [Stock_KLG]Quantity * Average( [Query1]Unit_Cost )

I tried with related and allexcept, but because of a 3'rd table it doesn't work.

Thank you!

[wdx223_Daniel · Accepted solution]

which is the 3rd table?

if you are adding a calculated column in DimProducts, can try this code

NewColumn=SUMX(RELATEDTABLE(Stock_KLG,Stock_KLG]Quantity)*AVERAGEX(RELATEDTABLE(Query1,Query1[Unit_Cost])

[Anonymous]

Great. Looks like it works in this form:

[Stock_KLG]Quantity * AVERAGEX(RELATEDTABLE(Query1,Query1[Unit_Cost])

Thank you!