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