Looking for DAX doing conditional calculation

[MonkeySam]

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 criteria and consolidate per the given weight. Below screenshot you can see M01 is given a special conversion than others:

Requests:

1. Is it possible to add conditions within "True Score %" measure and returns the result as "Converted Score %"? What I am looking for is this measure would reflect the average of "Converted Score %" when there is no any filter applied. For example, displying in a Card visualization.

2. Similar to above, is it possible to do one measure with Weight conversion per Metric ID?

My example PBIX file

[DataInsights · Accepted solution]

@MonkeySam,

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

[MonkeySam]

@DataInsights,

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

[DataInsights]

@MonkeySam,

Glad to hear the DAX works. Yes, I see that dual criteria now for M01. 🙂