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.
Answers
-
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
0 -
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?
0 -
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
0 -
@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?
0 -
Well, isn't that interesting...I will let Community know. Send me an email at rob.marshall@anaplan.com
1
