Forum Discussion

MonkeySam's avatar
MonkeySam
Frequent Visitor
5 years ago
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 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

 

 

Need Help
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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's avatar
      MonkeySam
      Frequent Visitor
      5 years ago

      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