Multiplication of two measures over two columns

[Anonymous]

Hi Team,

I have a table visual wherein I have two columns and two measures(sample data below).
pdt and cst are two columns from two different dimensions and m1 and m2 are two measures from different fact tables

pdt	cst	m1	m2
A	AA	20	10
B	BB	50	5
C	CC	50	20
D	DD	60	30

I need to multiply m1 and m2 row by row and then sum the entire values.Then divide it by sum of values of m1 measure.
For example:
20*10 = 200
50*5 = 250
50*20 = 1000
60*30 = 1800
and then SUM(200+250+1000+1800)/SUM(20+50+50+60) as the final value.

I have tried using SUMX  like below-
DIVIDE(SUMX(VALUES(TablePdt[pdt]),m1*m2),CALCULATE(m1, ALL(TablePdt[pdt])))

However, I actually have two columns here - pdt and cst, and I cannot use two tables in SUMX expression.

Is this requirement achievable? Please suggest

Any help would be highly appreciated.

Thanks,
Sam

[amitchandak · Accepted solution]

@Anonymous , Assume you have Table1, Table 2, and M1 and M2 are measures from those. And you have common dim1 and dim2

divide(Sumx(Summarize(Table1,dim1[pdt],dim2[cst],"_1",[M1]*[M2]),[_1]),[M2])

[Anonymous]

Thanks @amitchandak .It worked:)