Help with authoring formula

[Caitlin_Knox]

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 nest them here. If both of these conditions aren't met, then I'd like a BLANK returned.

Here's what I have so far

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

[Vvelarde · Accepted solution]

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

[Vvelarde · Accepted solution]

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

[Caitlin_Knox]

Maybe you can help with one more, related reqeust? 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'

Chubb = 
IF (
 'Actual Revenue'[PremiumPayableCode]="CHUIN2",
 SWITCH (
        'Actual Revenue'[PolicyAndLineTypes],
        "HOME", 'Actual Revenue'[LineEstimatedPremium] * .25,
        "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .25,
        "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .25,
       "PPKG/AUTO", 'Actual Revenue'[LineEstimatedPremium] *.25,
        "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .20
    ,'Actual Revenue'[LineEstimatedPremium]*.15

),

BLANK ()

)
   

[Sean · Accepted solution]

---

@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!

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!

[Caitlin_Knox]

Thank you!!

[Sean]

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

[CahabaData]

One approach I like is in not nesting.  Set each statement in its own column - i.e.

IF('Actual Revenue'[PolicyAndLineTypes]="HOME",'Actual Revenue'[LineEstimatedPremium]*.19,0)

This way you can then see each calculation in each column (or 0)

Then add a final calculation column that sums or selects which column is valid (depending on what you seek)

As you've discovered nesting beyond a couple levels becomes a pain to trouble shoot.  This method allows you to see each step of the chain of logic.