DAX measure to sum up product sales and divide

[OCBB_SFAFPandA]

Hello,

I have 2 separate FACTS tables, and do not have a dimension table to link the categories. I tried to manually create the relationship, but it would be many-to-many and would probably slow down the model. How can I create a measure for this?

Facts Sales
Product	Sales
A	100
B	150
C	110
D	500
E	300

Facts Qty sold
Product	Sold
A	10
B	15
C	20
D	50
E	10

I need to have a measure that sums all the product A (assume the tables are only for 1 day) and divide by Product A quantity sold. 

the table I am trying to make is

	A	B	C	D	E
Avg sale	Prod A Sales / Prod B qty

[Greg_Deckler · Accepted solution]

@OCBB_SFAFPandA Not sure if all the information is there but perhaps something like:

Measure = 
  VAR __Product = MAX('Facts Sales'[Product])
  VAR __Sales = AVERAGE('Facts Sales'[Sales])
  VAR __Sold = SUMX(FILTER('Facts Qty sold',[Product] = __Product),[Sold])
RETURN
  DIVIDE(__Sales, __Sold,0)

[OCBB_SFAFPandA]

Thanks this works @Greg_Deckler ! needed the sum instead of the avg, so just fixed it. Just so I understand, the reason we don't use sumx( or avgx) for the sales category is because it will be redundant if we are already filtering the product by the quantity sold expression? 

[Greg_Deckler]

@OCBB_SFAFPandA Right, so if you are using the Product column from the Fact Sales table in your visual, then the rows in that table are already being filtered down to that Product in the context of the visualization. So, no need to use SUMX/AVERAGEX where you include a FILTER clause like is being done in the Sold calculation.