Forum Discussion

JCBI1023's avatar
JCBI1023
Icon for Helper III rankHelper III
8 years ago
Solved

Calculate Sum with Multiple And Or Filters

Hello Masters, thank you for looking at this.

 

I have a measure that sums up all opportunities [# of Opportunities].

 

Each Opportunity has a Status and a Stage. 

 

Status: Won, Lost, Open

Stage: In Submittal, Etc

 

Won Opportunity = Status of Won or Open AND the Stage is In Submittal

 

Lost Opportunity = Status is Lost

 

Open Opportunity = Status is Open AND the Stage is NOT In Submittal

 

 

The "Lost" measure is easy: 

Lost = CALCULATE([# of Opportunities],FILTER('Opportunity Products Advanc','Opportunity Products Advanc'[Status (Opportunity)] = "Lost"))

 

Open Should look something like:

 

Open = CALCULATE([# of Opportunities],FILTER('Opportunity Products Advanc','Opportunity Products Advanc'[Status (Opportunity)] = "Open" || 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] <> "In Submittal"))

 

I don't think I am using "||" correctly because I am not getting the desired result.

 

 

For won, I am not sure how to use "OR"... Please help!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi JCBI1023

     

    If Open Opportunity requires both conditions, you should use AND(&&) instead of OR(||)
    Open Opportunity = Status is Open AND the Stage is NOT In Submittal

     

    Open =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
        'Opportunity Products Advanc'[Status (Opportunity)] = "Open"
            && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] <> "In Submittal"
    )
)



    For WON try this

    WON =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
        OR (
            'Opportunity Products Advanc'[Status (Opportunity)] = "Open",
            'Opportunity Products Advanc'[Status (Opportunity)] = "WON"
        )
            && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] = "In Submittal"
    )
)

     

     

  • parry2k's avatar
    parry2k
    8 years ago
    WON =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
         ('Opportunity Products Advanc'[Status (Opportunity)] = "Open" && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] = "In Submittal") ||
            'Opportunity Products Advanc'[Status (Opportunity)] = "WON"
        
            
    )
)

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi JCBI1023

     

    If Open Opportunity requires both conditions, you should use AND(&&) instead of OR(||)
    Open Opportunity = Status is Open AND the Stage is NOT In Submittal

     

    Open =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
        'Opportunity Products Advanc'[Status (Opportunity)] = "Open"
            && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] <> "In Submittal"
    )
)



    For WON try this

    WON =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
        OR (
            'Opportunity Products Advanc'[Status (Opportunity)] = "Open",
            'Opportunity Products Advanc'[Status (Opportunity)] = "WON"
        )
            && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] = "In Submittal"
    )
)

     

     

    • JCBI1023's avatar
      JCBI1023
      Icon for Helper III rankHelper III
      8 years ago

      Zubair_Muhammad We are VERY CLOSE, Thank you so much.

       

      So, let me rephrase what WON means...

       

      Won = Status is Won. In addition... Statuses that are Open but have a Stage of In Submittal ... should also be considered as Won.

       

      So, if the Status is Won, it;'s Won. Also, if, the Status is set to Open but the Stage is In Submittal then it's also won. 

       

      So this should be shown as 4 Won. It doesn't matter what the Stage is, if the status is Won... then it's Won. 

       

       

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        8 years ago
        WON =
CALCULATE (
    [# of Opportunities],
    FILTER (
        'Opportunity Products Advanc',
         ('Opportunity Products Advanc'[Status (Opportunity)] = "Open" && 'Opportunity Products Advanc'[Opportunity Stage (Opportunity)] = "In Submittal") ||
            'Opportunity Products Advanc'[Status (Opportunity)] = "WON"
        
            
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you! I was struggling with writing a measure for my report and this totally did the trick. You're a wizard. 👨‍💻