Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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" ) )
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.
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" ) )
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 = 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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |