Many to Many Sum in a single step?

NoahJ
NoahJ
in Modeling

I am in the middle of a model build and am running into a situation where I feel like there should be an efficient option to do a calculation, but I can't figure out a way to do it without a 'staging' line item that bloats the model. 

As the title says, I have a 'Many to Many' relationship - a single Deal can be tagged to multiple reps, and a single Rep can have multiple deals. I want to sum number values from the Deal dimension to the Rep dimension as simply and efficiently as possible. 

As an example: I have a 'M1' module dimensioned by Deal and Part with a Price line item, and an 'M2' module dimensioned by Reps and Deals that serves as a 'mapping' with a 'Tagged?' boolean line item indicating if that particular Rep is attached to that particular Deal. The goal is to get to the summary 'M3' module dimensioned by Rep and Part, displaying the totals of each part for all deals each Rep was attached to. 

 

NoahJ_4-1639085281896.png

 

Currently, in Anaplan the only way I can think to do it is to add a 'staging' line item in a new module that is dimensioned by Deal, Rep and Part, with the formula IF 'M2: Reps on Deal'.Tagged? THEN 'M1: Deal details by Part'.Price ELSE 0. Then that line item can have a summary value across All Deals top level item to give the correct value in M3.

However, this gets very inefficient in my actual implementation because of the large number of items each list. As we all know multiplying dimensions gets large quickly, and while the soon-to-arrive Polaris engine would help, it still feels like a lot spent without any 'new' information being added to the system.

Is there some trick that I am missing that would work? I have tried adding Rep or Deal formatted line items to M2 and trying to use them in SUMS/LOOKUPS, but always get the "Dimension of mapping used for aggregation doesn't match dimension of the source" error message (not too surprised). I know that I could use a numbered list to duplicate the Deals as children of the Reps, but am hoping to avoid the extra actions necessary for that. If not, do you think it would be worth a feature request, and what might that look like?

Thanks in advance!

 

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @NoahJ 

    Good question. My go to approach is to build a list of "valid" combinations of reps and deals. This serves two purposes. First, it allows me to establish the relationship between reps and deals. Unfortunately, Anaplan, like any relational database, needs an intersection table to resolve many to many. Second, it allows you to establish an allocation process where you can, for example, spread a deal value to multiple reps based on your business rules.

    The cons to this approach is that you have to regularly maintain this list using a combination of new combinations and combinations that are no longer valid.

    Just an idea for you. Not what you wanted to hear probably but its effective and doesn't require the need for a bloated module. Nice thing too is that you can use forms to generate new combinations rather than relying on a numbered list.

  • Misbah
    Misbah

    @NoahJ 

     

    Apart from what @JaredDolich mentioned, if Part List is a structural list then you can think of using SELECT/LOOKUP Statement on Part X and Part Y and pull the numbers into their own line items .

     

    Misbah

    Miz Logix

  • NoahJ
    NoahJ

    Thanks for the feedback @Misbah and @JaredDolich! Creating a valid combinations list would be the most efficient given a large scale, so I think I'll try to estimate our project list numbers and see if it is necessary.

Categories