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.
