topic Calculated Column from two tables related to one another with a 3'rd table in DAX Commands and Tips

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

Anonymous — Tue, 31 Jan 2023 07:30:29 GMT

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 — Tue, 31 Jan 2023 07:41:42 GMT

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 — Tue, 31 Jan 2023 08:05:12 GMT

Great. Looks like it works in this form:

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

Thank you!