Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to 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 and @amirabedhiafi what is the difference between the solutions you provided?
I am curious if it doesn't give the same result ?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |