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.
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?
@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?