Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JCBI1023
Helper III
Helper III

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

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!

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

@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. 

 

Won.png

 

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.