Forum Discussion

martti's avatar
martti
Icon for Helper I rankHelper I
6 years ago
Solved

Measure to calculate only for certain non-numeric values

I have a measure that calculates a result from multiple different measures for different employees, e.g. Measure0 = ( [Measure1] + [Measure2] ) / 2 + [Measure3] My intent is for Measure3...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi martti ,

     

    When using measures you need to be aware that you cannot reference tables/column directly and you need to use aggregators in your case if you use the MAX function for the if statment that should work.

     

    Measure0 =
 ( [Measure1] + [Measure2] ) / 2
    + IF ( MAX ( Tbl_employees[Group] ) = "Group1"; [Measure3]; 0 )

     

    You can also try this variant of the calculation should work in the same way:

     

    Measure0 =
( [Measure1] + [Measure2] ) / 2
    + CALCULATE (
        [Measure3];
        FILTER (
             Tbl_employees;
            Tbl_employees[Group] ="Group1"
        )
    )

     

    Regards,

    MFelix 

martti's avatar
martti
Icon for Helper I rankHelper I
6 years ago

I figured that referencing to the column directly would not be an option, but never thought about using MAX() for the reference. It worked exactly as intended, thanks!

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi martti ,

 

You can also use SELECTEDVALUE, MIN, or other similar notation.

 

However not really sure how you want to present the information but if you want to have it in a table you need to use the second formula I presented since the sum of the values using the IF statement will get you an incorrect result due to context.

 

Regards,

MFelix