Forum Discussion

Elder22's avatar
Elder22
Frequent Visitor
9 years ago
Solved

Nested IF formulas with OR statement

Hello,   I have tried to research this issue on a few posts but cannot apply the solutions to solve my problem.   I am trying to measure opportunities against two seperate sets of criteria, I wan...
Need Help
  • kcantor's avatar
    kcantor
    9 years ago

    Elder22

    Break it down into two if statements using 1 for the positive result and 0 for the negative result. Then write a third if statement referencing the two if statements to week out the negatives. Finally, use that last statement in a Calculate.

     Sample = CALCULATE([Total Orders], [Both Criteria]=1)

    Of course if you provide sample data or a snip we can better help you solve this. I am just spitballing ideas based solely upon your question.

     

  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Elder22,

     

    Could you please provide us more detail information about your data model? Generally, we can add multiple conditions in IF function, such as

    Measure  =  IF((Condition1 && Condition2 && Condition3) || (Condition4 && Condition5), "Ture value","False Value")

     

    Regards,

    Charlie Liao

Elder22's avatar
Elder22
Frequent Visitor
9 years ago

Hi all,

 

Apologies about the delay on this - after several iterations based on some great help from the minds here I managed to solve the problem.

 

I used three new columns (perhaps not the most efficient) -

Commit bucket = IF(AND(Opportunity[DateDiff Close date]=0,Opportunity[ForecastCategory]="Forecast"),Opportunity[Total orders],0)

 

Closed Won bucket = IF(AND(Opportunity[DateDiff Support sub]=0,Opportunity[Stage Name]="Closed Won"),Opportunity[Total orders],0)

 

To add together to make this:

 

Forecast (Commit number) = Opportunity[Closed Won bucket] + Opportunity[Commit bucket]

 

This allows me to run two seperate counts in the first columns, and then add together a total with the final one.

 

Thanks for all the help