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 St...
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"
        
            
    )
)
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. 👨‍💻