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.
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.
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
@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]))
@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.
Hi,
Check it and let me know whether my result is correct or not.
Hi,
Share some data and show the expected result.
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%.
Hi,
Does this measure work?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |