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

 

 

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