Calculating the average rate based on specific conditions

[AmiraBedh]

I have the following T-SQL query :

SELECT
(SUM(CASE WHEN DFP.status = 'ON'
THEN FVD.metric_rep * FVD.consumption
ELSE 0 END) /
SUM(CASE WHEN DFP.status = 'ON'
THEN FVD.consuption
ELSE 0 END)) * 100 AS RateRTC

FROM FactVenteDetails AS FVD
LEFT JOIN DimStatus AS DFP
ON FVD.fk_status = DFP.sk_status

The query is defining the average rate based on this formula : 

100x (M1xTC1 + M2xTC2 ....)​/(M1+M2 ...)

Where M is the FVD.metric_rep and the TC is theFVD.consumption

How can I translate it to DAX ?

[AmiraBedh]

@amitchandak  and @amirabedhiafi  what is the difference between the solutions you provided?

I am curious if it doesn't give the same result ?

[amirabedhiafi · Accepted solution]

YourMeasure =

var test =

CALCULATE(SUM(FVD[metric_rep] * FVD[consumption] ), DFP[status] = "ON")

var test2 = CALCULATE(sum(FVD[consuption]),DFP[status] = "ON")

return  DIVIDE(test,test2,0)

[amitchandak]

@AmiraBedh ,

calculate( divide( Sumx(FVD,FVD[metric_rep] * FVD[consumption]) ,sum(FVD[consuption])), Filter(DFP , DFP[status] = "ON") )

You need have a join between two tables in power bi