Sum from list item property

I have a list of accounts and a list of account managers, broken into three categories (IVAM, DIAM, CCAM). Sales managers must assign an account manager from each category to every account. I want to aggregate the account metrics for all the accounts managers in a single table. The first image below shows the account assignment module, the second is the aggregation table. Currently, my solution is something like:

Total 3yr Avg OIT: IF AM Type = AM Type.CCAM THEN 'Account Assignments'.'3yr Avg OIT'[SUM: 'Account Assignments'.CCAM] ELSE IF AM Type = AM Type.IVAM THEN 'Account Assignments'.'3yr Avg OIT'[SUM: 'Account Assignments'.IVAM] ELSE IF AM Type = AM Type.DIAM THEN 'Account Assignments'.'3yr Avg OIT'[SUM: 'Account Assignments'.DIAM]

This solution works, but is a very brute force formula and inelegant. Any ideas on how to improve this? If we end up expanding account manager categories or metrics, this solution does not scale well.

dameyer01_0-1605564968496.png

dameyer01_2-1605565137860.png

 

 

Answers

  • @dameyer01 

     

    Create a module that is dimensionalized by Position Type.  The formula will be Account Assignments'.'3yr Avg OIT'[SUM: 'Account Assignments'.Position Type] as long as Position Type is list formatted.

     

    Rob

  • @rob_marshall 

     

    I might not have explained the situation correctly. The 'IVAM', 'DIAM', and 'CCAM' columns in the account assignment table are line items (list-formatted by the Account Managers list). Position type is being maintained as a property of each account manager position in a module. If I create a module dimensionalized by Position Type, how do I reference the position type of each account manager in the SUM statement?

  • @dameyer01 

     

    Maybe we can get on a phone just to make sure I am understanding you correctly.  But you should be able to do a sum off the SYS module where you have Position Type as a line item.

     

    Feel free to DM and you can explain the missing pieces.

     

    Rob

  • @rob_marshall - Sorry, but I can't figure out how to send you a DM. When I click the envelope icon on the top-right, I get a "Sorry, this content no longer exists" page. Is there a different link I should use?

  • @dameyer01 

     

    Well, isn't that interesting...I will let Community know.  Send me an email at rob.marshall@anaplan.com