win rate with filter

[Anonymous]

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 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)    Any help is much appreciated.  

WinRate = DIVIDE (

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )

    ),

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER (

            ALL ( 'AllOpps-Products' ),

            'AllOpps-Products'[New Status] = "Won"

                || 'AllOpps-Products'[New Status] = "Lost"

        )

    )

)

[Anonymous]

HI @Anonymous,

If you want to keep the original filter effects on your formulas, I'd like to suggest you use allselected function replace all. (notice: all function will ignore current filter effects)

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

Regards,

Xiaoxin Sheng

[amitchandak]

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

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

Hi,

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

@Ashish_Mathur 

The formula below looks at ALL of the won opportunities versus the won/lost.     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.  

So, for example, our overall win rate might be 64%.     But our win rate for deals over $1 million might only be 38%.  

WinRate = DIVIDE (

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )

    ),

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER (

            ALL ( 'AllOpps-Products' ),

            'AllOpps-Products'[New Status] = "Won"

                || 'AllOpps-Products'[New Status] = "Lost"

        )

    )

)

[Ashish_Mathur]

Hi,

Does this measure work?

WinRate = DIVIDE (

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" && 'AllOpps-Products'[Description] >= 1000000)

    ),

    CALCULATE (

        COUNT ( 'AllOpps-Products'[Description] ),

        FILTER (

            ALL ( 'AllOpps-Products' ),

            ('AllOpps-Products'[New Status] = "Won"

                || 'AllOpps-Products'[New Status] = "Lost") && 'AllOpps-Products'[Description] >= 1000000

        )

    )

)