win rate with filter

Question

Hello PBI Community, &nbsp;&nbsp;&nbsp;I have the current win rate formula but would like to constrain it (via formula) to only those opportunities that are above $1,000,000. &nbsp;&nbsp;I can then compare it to our overall win rate to see how we are doing overall versus the win rate of just our larger opportunties (which is a much smaller cohort) &nbsp;&nbsp; Any help is much appreciated. &nbsp;&nbsp;WinRate = DIVIDE (&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( 'AllOpps-Products' ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'AllOpps-Products'[New Status] = "Won"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || 'AllOpps-Products'[New Status] = "Lost"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; ))

ashish_mathur · Answer

Hi,
Share some data and show the expected result.

anonymous · Answer

Ashish_Mathur&nbsp;&nbsp;The formula below looks at ALL of the won opportunities versus the won/lost. &nbsp; &nbsp; I simply want the same basic result...but with a filter that only looks at those opportunities ("Description") that are $1,000,000 or above. &nbsp;&nbsp;So, for example, our overall win rate might be 64%. &nbsp; &nbsp; But our win rate for deals over $1 million might only be 38%. &nbsp;&nbsp;WinRate = DIVIDE (&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( 'AllOpps-Products' ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'AllOpps-Products'[New Status] = "Won"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || 'AllOpps-Products'[New Status] = "Lost"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; ))

amitchandak · Answer

Anonymous , Try like
if opportunities value is a column where value should be &gt; 1000000
WinRate = DIVIDE (
CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" &amp;&amp; 'AllOpps-Products'[New Status]&gt;1000000 )
),
CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER (
ALL ( 'AllOpps-Products' ),
'AllOpps-Products'[New Status] in{ "Won", "Lost"} &amp;&amp; 'AllOpps-Products'[New Status]&gt;1000000 )
)
)
if this a opportunities value is a measure 
Averagex(filter(summarize( 'AllOpps-Products','AllOpps-Products'[opportunity_id],"_1" ,sum('AllOpps-Products'[opportunities value]),
"_2",CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )
) ,"_3", CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER (
ALL ( 'AllOpps-Products' ),
'AllOpps-Products'[New Status] in{ "Won", "Lost"} )
)),[_1] &gt;1000000 ) Divide([_2],[_3]))

ashish_mathur · Answer

Hi,
Does this measure work?
WinRate = DIVIDE (
&nbsp;&nbsp;&nbsp; CALCULATE (
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" &amp;&amp;  'AllOpps-Products'[Description] &gt;= 1000000)
&nbsp;&nbsp;&nbsp; ),
&nbsp;&nbsp;&nbsp; CALCULATE (
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT ( 'AllOpps-Products'[Description] ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( 'AllOpps-Products' ),
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ('AllOpps-Products'[New Status] = "Won"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || 'AllOpps-Products'[New Status] = "Lost") &amp;&amp; 'AllOpps-Products'[Description] &gt;= 1000000
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;&nbsp;&nbsp; )
)

anonymous · Answer

amitchandakand&nbsp;Ashish_Mathur &nbsp; &nbsp; Hi Guys - Thank you for the feedback. &nbsp;&nbsp;Amit - The measure formula actually gave me the exact same result as my original formula, which is strange. &nbsp; The column formula did not result in a percent...but a dollar value. &nbsp;&nbsp; I changed the "new status" to "sales potential" in the last part of the formulas because that is the value field, but even after converting to a percent, the answer was 754% which is not correct. &nbsp;&nbsp;&nbsp;Ashish - Your formula resulted in a number of 35.7% which is slightly lower than our overall 39.76% (overall win rate, depending on the formula I use). &nbsp; It could be correct, but I'd want to check it manually. &nbsp; &nbsp; If I use a different formula (my original formula I posted), I get a win rate of 86%. &nbsp; I beleive the difference is the formula below is calculating won opportunities versus all opportunities (dividing "won" opportunities over all opportunties). &nbsp;&nbsp; The other formula is calculating won versus&nbsp; won/lost. &nbsp;&nbsp;Win Rate = CALCULATE(COUNT('AllOpps-Products'[Description]),FILTER('AllOpps-Products',[New Status]="WON")) / CALCULATE(COUNT('AllOpps-Products'[Description]),ALL('AllOpps-Products'))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

win rate with filter

7 Replies

Related content

Utilization Rate

Currency FX Rates

Adding an intererst rate

Exchange Rates - Historical

Calculate Alarm Rate