Data Extraction from Unrelated tables

[Swam80]

Hello all,

• I have 3 tables, fact sales momthly, fact gross price and dim products.
• dim products have one to many relation with both fact tables but the fact tables do not have any relation between them.
• fact_sales_monthly have sales quantity column and fact_gross_price have gross_price column. I want to calculate sales_amount that is sales quantity*gross_price.

How to do it in dax or any other way ?
I tried using RELATED(), CROSSFILTER(), LOOKUP(), didnt work out.

I have attached the image of my datamodel.

[Greg_Deckler · Accepted solution]

@Swam80 I would think a column in your dim_product table like this should work:

Column =
  VAR __Sold = SUMX(RELATEDTABLE('gdb023 fact_sales_monthly'), [sold_quantity])
  VAR __Price = SUMX(RELATEDTABLE('gdb023 fact_sales_price'), [gross_price])
  VAR __Result = __Sold * __Price
RETURN
  __Result