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 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())))))

Need Help

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
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:

6 Replies

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

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:

Caitlin_Knox
Advocate III
9 years ago
Thank you!!
Caitlin_Knox
Advocate III
9 years ago
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
  Community Champion
  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
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.

Forum Discussion

Help with authoring formula

6 Replies

Related content

If formula

formula

DAX Formula Help

Kudos and the Top Kudoed Authors widget

DAX Formula