cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rrose88 Frequent Visitor
Frequent Visitor

Calculate SUM with Multiple AND OR crtieria

Hello - I would like to create a measure based on the below but it doesn't seem to work as intended. I want to find the Est. revenue if the forecast category is "commit" or if it's "closed" AND the status is 1.

 

Est. Revenue =
CALCULATE(SUM(Opportunity[Revenue]),
FILTER(Opportunity,[Forecast]=Commit || Opportunity[Forecast]=Closed && [StateCode]=1 )
)

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
tarunsingla Established Member
Established Member

Re: Calculate SUM with Multiple AND OR crtieria

What is the error message you getting?

 

Try enclosing the text values in quotes, like this:

Est. Revenue =
CALCULATE(SUM(Opportunity[Revenue]),
FILTER(Opportunity,[Forecast]="Commit" || Opportunity[Forecast]="Closed" && [StateCode]=1))

 

Also, make sure you have enclosed the || && conditions as desired, using the parenthesis e.g.

FILTER(Opportunity, ([Forecast]=Commit || Opportunity[Forecast]=Closed) && [StateCode]=1 )

 

1 REPLY 1
tarunsingla Established Member
Established Member

Re: Calculate SUM with Multiple AND OR crtieria

What is the error message you getting?

 

Try enclosing the text values in quotes, like this:

Est. Revenue =
CALCULATE(SUM(Opportunity[Revenue]),
FILTER(Opportunity,[Forecast]="Commit" || Opportunity[Forecast]="Closed" && [StateCode]=1))

 

Also, make sure you have enclosed the || && conditions as desired, using the parenthesis e.g.

FILTER(Opportunity, ([Forecast]=Commit || Opportunity[Forecast]=Closed) && [StateCode]=1 )