Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

win rate with filter

Hello PBI Community,      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.     I can then compare it to our ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try like

if opportunities value is a column where value should be > 1000000

WinRate = DIVIDE (
CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" && 'AllOpps-Products'[New Status]>1000000 )
),
CALCULATE (
COUNT ( 'AllOpps-Products'[Description] ),
FILTER (
ALL ( 'AllOpps-Products' ),
'AllOpps-Products'[New Status] in{ "Won", "Lost"} && 'AllOpps-Products'[New Status]>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] >1000000 ) Divide([_2],[_3]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandakand Ashish_Mathur     Hi Guys - Thank you for the feedback.  

     

    Amit - The measure formula actually gave me the exact same result as my original formula, which is strange.   The column formula did not result in a percent...but a dollar value.    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.   

     

    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).   It could be correct, but I'd want to check it manually.     If I use a different formula (my original formula I posted), I get a win rate of 86%.   I beleive the difference is the formula below is calculating won opportunities versus all opportunities (dividing "won" opportunities over all opportunties).    The other formula is calculating won versus  won/lost.  

     

    Win Rate = CALCULATE(COUNT('AllOpps-Products'[Description]),FILTER('AllOpps-Products',[New Status]="WON")) / CALCULATE(COUNT('AllOpps-Products'[Description]),ALL('AllOpps-Products'))

     

     

     

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Check it and let me know whether my result is correct or not.