Many to Many Sum in a single step?

Options
NoahJ
NoahJ Member, ALL USERS, GroupMember, Master Anaplanner - Employee, Certified Master Anaplanner, Employee, Certified Model Builder Posts: 5 Certified Master Anaplanner
in Planning & 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:

Comments

  • NoahJ
    NoahJ Member, ALL USERS, GroupMember, Master Anaplanner - Employee, Certified Master Anaplanner, Employee, Certified Model Builder Posts: 5 Certified Master Anaplanner

    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