Forum Discussion
Solved
Looking for DAX doing conditional calculation
Hi All, I am looking for one or two meaures to do conditional calculation(s) instead of building up separate measures. It's about sort of scoring but requires further conversion per metric crite...
Try these measures. I added a Weight column to your table.
Converted Score % = VAR vTable = ADDCOLUMNS ( VALUES ( 'Dataset'[Metric ID] ), "@TrueScore", [True Score %] ) VAR vResult = AVERAGEX ( vTable, IF ( 'Dataset'[Metric ID] = "M01", 1, [@TrueScore] ) ) RETURN vResult Weighted Score % = VAR vTable = ADDCOLUMNS ( VALUES ( 'Dataset'[Metric ID] ), "@ConvertedScore", [Converted Score %], "@Weight", CALCULATE ( MAX ( 'Dataset'[Weight] ) ) ) VAR vResult = SUMX ( vTable, [@ConvertedScore] * [@Weight] ) RETURN vResult
Try these measures. I added a Weight column to your table.
Converted Score % =
VAR vTable =
ADDCOLUMNS ( VALUES ( 'Dataset'[Metric ID] ), "@TrueScore", [True Score %] )
VAR vResult =
AVERAGEX ( vTable, IF ( 'Dataset'[Metric ID] = "M01", 1, [@TrueScore] ) )
RETURN
vResult
Weighted Score % =
VAR vTable =
ADDCOLUMNS (
VALUES ( 'Dataset'[Metric ID] ),
"@ConvertedScore", [Converted Score %],
"@Weight", CALCULATE ( MAX ( 'Dataset'[Weight] ) )
)
VAR vResult =
SUMX ( vTable, [@ConvertedScore] * [@Weight] )
RETURN
vResult
Many thanks for your support. Your DAX calculations hit the goal!! Just let you know that I added a small condition on "Converted Score %" in order to achieve M1's scenarios.
Converted Score % =
VAR vTable =
ADDCOLUMNS ( VALUES ( 'Dataset'[Metric ID] ), "@TrueScore", [True Score %] )
VAR vResult =
//AVERAGEX ( vTable, IF ( 'Dataset'[Metric ID] = "M01", 1, [@TrueScore] ) )
AVERAGEX (
vTable,
IF ( 'Dataset'[Metric ID] = "M01" && [@TrueScore] >= 0.5, 1, [@TrueScore] )
)
RETURN
vResult