IF statement in a measure

[adam_macs]

Hi, i am trying to write an IF statement in a measure to essentially look at the row and determine if its international or UK. If its international then it should minus the YTD retention % by 0.82, if its UK then minus the YTD retention % by 0.90.

The results i am getting are completly wrong which is due to the use of SUMX. Is there another way i can get this IF statement to work?

Retention Target Diff = 
SUMX(CountryMapping,
  IF(CountryMapping[Region_L1]="United Kingdom",
    [ACY21 _Retention_YTD]-0.9,
    [ACY21 _Retention_YTD]-0.82))

[AlexisOlson · Accepted solution]

I think you intend to sum over regions rather than the rows of CountryMapping.

Try this instead:

Retention Target Diff =
SUMX (
    VALUES ( CountryMapping[Region_L1] ),
    IF (
        CountryMapping[Region_L1] = "United Kingdom",
        [ACY21 _Retention_YTD] - 0.9,
        [ACY21 _Retention_YTD] - 0.82
    )
)

[ValtteriN]

Hi,

If your YTD measure is working as intended this structure should do it:

Measure = IF(max(CountryMapping[Region])="United Kingdom",
[ACY21_Retention-YTD]-0.9,[ACY21_Retention-YTD]-0.82)

I hope this helps to solve your issue and if it deos consider accepting this as  a solution!