topic Re: DAX measure to sum up product sales and divide in DAX Commands and Tips

DAX measure to sum up product sales and divide

OCBB_SFAFPandA — Tue, 16 Aug 2022 17:06:16 GMT

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

Re: DAX measure to sum up product sales and divide

Greg_Deckler — Tue, 16 Aug 2022 17:19:49 GMT

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)

Re: DAX measure to sum up product sales and divide

OCBB_SFAFPandA — Tue, 16 Aug 2022 23:50:19 GMT

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?

Re: DAX measure to sum up product sales and divide

Greg_Deckler — Wed, 17 Aug 2022 13:17:05 GMT

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.