Skip to main content

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

J

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.

    2 replies

    A
    • AlliAlosa
    • Inspiring
    • 382 replies
    • December 30, 2019

    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:

    J
    1 person likes this
    • J
      Justin_Barrett
    J
    • Jerry_Argyriou
    • Author
    • New Participant
    • 4 replies
    • December 30, 2019
    AlliAlosa wrote:

    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!

    A
    1 person likes this
    • A
      AlliAlosa

    Reply


    Most helpful members this week

    Terms & Conditions