Skip to main content

Context: CRM probability assignment to opportunity. Complex value chain, multiple stakeholders resulting in 2 variables (dealtype & stage) required to calc probability. Both variables are single-select fields.

I’m desperately trying to avoid nested-ifs as its a recipe for a maintenance disaster. I tried nesting Swtich() - no luck.

I’ve imported and created this table, and even created separate table with rows [dealtype#-stage#, % ]. Neither seem to work as I cannot select a formula field to lookup off probability table.

Any guidance on how to solve this highly appreciated.

Hi there, this might help point you in the right direction… though it’s not perfect.

The matrix is defined on the [Probability Matrix] table. All of those records are linked to a single record on the [Junction] table. In turn, all of the records on the [Opportunities] table must also be linked to the same record on the [Junction] table to get this to work properly.

The [Junction] table contains a rollup field which puts all possible combinations of {Deal Type} and {Stage} into an array. Then, on the [Opportunities] table, that array is parsed via a MID() formula to find a matching {Deal Type} and {Stage}, and return the associated percentage.

Hope this is helpful to get you started! :slightly_smiling_face:

Hi there, this might help point you in the right direction… though it’s not perfect.

The matrix is defined on the [Probability Matrix] table. All of those records are linked to a single record on the [Junction] table. In turn, all of the records on the [Opportunities] table must also be linked to the same record on the [Junction] table to get this to work properly.

The [Junction] table contains a rollup field which puts all possible combinations of {Deal Type} and {Stage} into an array. Then, on the [Opportunities] table, that array is parsed via a MID() formula to find a matching {Deal Type} and {Stage}, and return the associated percentage.

Hope this is helpful to get you started! :slightly_smiling_face:


Great, thank you!! This seems like a far more elegant solution compared to my 31 line (!!) function of nested-IFs clustered within a SWITCH function’s options. Will try it and revert.
Regardless, you’ve definitely moved my solutions-toolbox forward a couple of notches. Thank you!

Terms & ConditionsAccessibility statement