Plan probability figures are unexpectedly large

Hi Joshua,

The plan probability is recorded on both the plan data and on the step data. This is so that when doing step-based reporting, one can still easily access the probability of the plan to which that step is associated. I'm not clear yet on your goals, but probably we won't want to SUM our probability as that will likely result in values greater than 100%. An average plan probability seem more like what you're after.

The simplest way to get there, assuming your widget is solely focused on the plans and not their steps, is to add a filter on your widget that limits the data to the Plans and excludes the Steps. To do this, search for the field [Plan or Step] and then select from that filter to only include [Plan]. With that, you can use the AVG Probability Amount without issue.

If you need to include Step data in this widget, but need an overall Plan Probability, then using the built in AVG() will not behave as you're suspecting. It will add the probability across all rows (Plans and Steps) and then average by the number of plans plus the number of steps. So if we have 2 plans, 1 at 50% with 2 steps and 1 at 90% with 9 steps, then the AVG() function will add up 50% x 3 (1 plan + 2 steps) + 90% x 10 (1 plan + 9 steps) for a total probability of 1050%, divided by 13 rows is 80.77%. When what we really want is 50% + 90% / 2 plans = 70%.

Instead of using the AVG() function then, we can add our own formula. First we total the Plan Probability with a Value Filter on [Plan or Step] set to include only [Plan]. So this will return the Plan Probability on only the Plan (not the Steps) and then SUM those. Then we divide that by the [Plan Count]. In the image below, Total Plan Probability is a simple SUM(), Average Plan Probability is a simple AVG(), while the "Formula" value is the bespoke formula as described above.

Best,
Chris

Hi Joshua,

The plan probability is recorded on both the plan data and on the step data. This is so that when doing step-based reporting, one can still easily access the probability of the plan to which that step is associated. I'm not clear yet on your goals, but probably we won't want to SUM our probability as that will likely result in values greater than 100%. An average plan probability seem more like what you're after.

The simplest way to get there, assuming your widget is solely focused on the plans and not their steps, is to add a filter on your widget that limits the data to the Plans and excludes the Steps. To do this, search for the field [Plan or Step] and then select from that filter to only include [Plan]. With that, you can use the AVG Probability Amount without issue.

If you need to include Step data in this widget, but need an overall Plan Probability, then using the built in AVG() will not behave as you're suspecting. It will add the probability across all rows (Plans and Steps) and then average by the number of plans plus the number of steps. So if we have 2 plans, 1 at 50% with 2 steps and 1 at 90% with 9 steps, then the AVG() function will add up 50% x 3 (1 plan + 2 steps) + 90% x 10 (1 plan + 9 steps) for a total probability of 1050%, divided by 13 rows is 80.77%. When what we really want is 50% + 90% / 2 plans = 70%.

Instead of using the AVG() function then, we can add our own formula. First we total the Plan Probability with a Value Filter on [Plan or Step] set to include only [Plan]. So this will return the Plan Probability on only the Plan (not the Steps) and then SUM those. Then we divide that by the [Plan Count]. In the image below, Total Plan Probability is a simple SUM(), Average Plan Probability is a simple AVG(), while the "Formula" value is the bespoke formula as described above.

Best,
Chris

Hi Chris,

This looks much better without the step progress as well - thanks!

KR

Josh