Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Probability matrix value driven by two "single-select" fields / nested-ifs / multi-variate lookup

1866 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Jerry_Argyriou
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
qqqq

Any guidance on how to solve this highly appreciated.

2 Replies 2
AlliAlosa
10 - Mercury
10 - Mercury

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!