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:

CahabaData's avatar
CahabaData
Memorable Member
9 years ago

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.