Forum Discussion
Calculating the average rate based on specific conditions
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 ?
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)
3 Replies
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)
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
amitchandak and amirabedhiafi what is the difference between the solutions you provided?
I am curious if it doesn't give the same result ?
