Forum Discussion

Caitlin_Knox's avatar
Caitlin_Knox
Advocate III
9 years ago
Solved

Help with authoring formula

I'm trying to write an IF AND statement and think my logic might be off.. The first condition is that PPAutoOnwers need to equal '1'. The second condition has multiple conditions, and I've tried to n...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hi Caitlin_Knox

     

    please try with this:

     

    Auto Owners =
IF (
    'Actual Revenue'[PPAutoOwners] = "1",
    SWITCH (
        'Actual Revenue'[PolicyAndLineTypes],
        "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16,
        "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16,
        "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19
    ),
    BLANK ()
)

     Let me know if helps

  • Sean's avatar
    Sean
    9 years ago
    Caitlin_Knox wrote:

    Am I correct in my syntax below if I want to use .15 for all else that still meet the first condition?

     

    So the Premium Payable Code would still need to equal CHUIN2,

     

    but if the Policy and LIne type is different that what is defined in the expression,

     

    then the line estimated premium should be multiplied by .15 as a 'default'

     


    Caitlin_Knox

    Yes! You Got it! :smileyhappy:

     

    You will get values only if Premium Payable Code is CHUIN2 ! All other Premium Paybale Codes will be BLANK()

     

    then ALL Policy and Line Types if they match the ones you've listed in the SWITCH will be multiplied accordingly

     

    and if they do not match any of the 5 you listed in the SWITCH will be multiplied by 0.15

     

    Great job! :smileyhappy:

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

hi Caitlin_Knox

 

please try with this:

 

Auto Owners =
IF (
    'Actual Revenue'[PPAutoOwners] = "1",
    SWITCH (
        'Actual Revenue'[PolicyAndLineTypes],
        "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16,
        "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16,
        "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19
    ),
    BLANK ()
)

 Let me know if helps

Sean's avatar
Sean
Community Champion
9 years ago

Yep that's what I came up with too - except with SWITCH ( TRUE () - you have to repeat the column name

 

Auto Owners =
IF (
    'Actual Revenue'[PPAutoOwners] = "1",
    SWITCH (
        TRUE (),
        'Actual Revenue'[PolicyAndLineTypes] = "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        'Actual Revenue'[PolicyAndLineTypes] = "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19,
        'Actual Revenue'[PolicyAndLineTypes] = "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16,
        'Actual Revenue'[PolicyAndLineTypes] = "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16,
        'Actual Revenue'[PolicyAndLineTypes] = "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19,
        BLANK ()
    ),
    BLANK ()
)

Vvelardeis lightning fast :smileyhappy: