Using SUMPRODUCT in dax

[Anonymous]

Hi All,

I would like to compile a similar table to PowerBI but I need to calculate the totals using DAX. I'm running to an issue especially with the row level context and the total Avg amounts for both Products A & B as I use SUMPRODUCT(Product A:AVG Amount ProductA)/SUM(Product A) = $22.67

Expected result Total AVG amount per month (Jan) = ((80*22)+(64*20))/144 = $21.11
Entire year Avg amount =( (3282*19.90)+(2798*19.90))/ (3282+2798)

	Product A	Product B	Avg Amount- Prod A	Avg Amount- Prod B	Avg Amount -All Products
Jan	80	64	22	20	21.11
Feb	41	211	22	17	17.81
Mar	58	200	22	22	22.00
Apr	879	233	22	22	22.00
May	88	400	23	23	23.00
Jun	89	74	23	15	19.37
Jul	547	146	23	19	22.16
Aug	742	874	23	20	21.38
Sep	214	20	23	17	22.49
Oct	200	222	23	14	18.27
Nov	100	100	23	22	22.50
Dec	244	254	23	20	21.47
Total	3282	2798	22.68	19.90	21.40

Please help create a measure 

[lbendlin · Accepted solution]

see attached. Note that your source data structure is not sustainable.