Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calculate a win rate. I have two Columns (1) OldValue and (2) is Opportunity.IsWon.
The OldValue column contains Stages (i.e. stage 1, stage 2, stage 3). The Opportunity.IsWin column contains either True or False.
I want to be able to calculate the Grand Total of Opps where the old value is Stage1. Let's say, for example, that it comes out to 100 total opps.
I then need to calculate how many of those opps had an Opportunity.IsWon as True. And finally with that I want to calculate the % of wins based on how many opps were true / the total opps.
Thank you for your help!
Solved! Go to Solution.
I'll do this with 3 measures.
Grand Total of Opps = CALCULATE( COUNTROWS('YourTable'), LEFT('YourTable'[OldValue], 7) = "Stage 1" )
Opps had an Opportunity = CALCULATE( COUNTROWS('YourTable'), LEFT('YourTable'[OldValue], 7) = "Stage 1", 'YourTable'[Opportunity.IsWon] )
WinRate = DIVIDE( [Opps had an Opportunity], [Grand Total of Opps] )
Assumptions:
- Your field Opportunity.IsWin is of type boolean, rather than text
- You wanted to hardcode "Stage 1" into your measure.
I'll do this with 3 measures.
Grand Total of Opps = CALCULATE( COUNTROWS('YourTable'), LEFT('YourTable'[OldValue], 7) = "Stage 1" )
Opps had an Opportunity = CALCULATE( COUNTROWS('YourTable'), LEFT('YourTable'[OldValue], 7) = "Stage 1", 'YourTable'[Opportunity.IsWon] )
WinRate = DIVIDE( [Opps had an Opportunity], [Grand Total of Opps] )
Assumptions:
- Your field Opportunity.IsWin is of type boolean, rather than text
- You wanted to hardcode "Stage 1" into your measure.
@Anonymous Thank you so much for the quick response and providing the breakdown. This worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |