Calculate Sum with Multiple And Or Filters

[JCBI1023]

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!

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

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]

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

[JCBI1023]

@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 · Accepted solution]

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