Sum from a model Applies to Fiscal Year and a customized numbering list to another w numbered list

CommunityMember96641

Hi Experts

Appreciate your prompt help to provide a solution!

We are working on Quota Management based on Territory hierarchy.

In module A (Applied to Fiscal Year, Mapping List with items like PSM1, PSM2, ..PSM1600), users can assign different territories to one employee assignment (employee assignment is not the same as employee - one employee can have multiple assignments of roles in different time frames) which create multiple lines for the same employee assignment. There is another module containing all quotas assigned to different levels of territories. So once the territories are assigned to an employee assignment, in module A, the quota of that territory will automatically pop up to the employee assignment with that territory.

In module B (Applied to Fiscal Period, Mapping List with items like PSM1, PSM2, ..PSM1600), based on the time frames for each assignment and the annual quotas from the territories, it calculates whether each period will have quota and how much it is.

In module C (Applied to Employee Assignment, Fiscal Period), we would like to sum up the quotas from multiple territories under the same Employee Assignment together. But we could not find a way to do it. Numbers are not coming over.

We know it could also be we sum module A to Employee Assignment first on annual quota and then calculate into periods later.

To sum, the challenge is "how to sum the quotas from a module of this Mapping list hosting multiple territories assigned to one employee assignment to a module of the Employee Assignment list hosting the total quota for each employee assignment.

Thanks a lot!

CommunityMember96641

Adding a note: Employee Assignment is a Numbered List.

Misbah

@CommunityMember96641

Coudld you please share the dimensionality of Module A, B and C. Also share what forumula are writing in Module C. I think the issue may be that you are using SUM function when the source module has NATIVE TIME in it.

Misbah

Miz Logix

CommunityMember96641

Thank you Misbah for your quick response!

Module A

Module B

Module C

In module C, the formula for Annual Quota (USD) is currently:

'Module B'.'Period Quota (USD)'[LOOKUP: Fiscal Year]

I tried to use BELOW, but neither worked:

'Module B'.'Period Quota (USD)'[LOOKUP: Fiscal Year, SUM: EmployeeAssignment]

'Module B'.'Period Quota (USD)'[LOOKUP: Fiscal Year, LOOKUP: EmployeeAssignment]

Misbah

@CommunityMember96641

Since you are pulling the numbers from Mod B, you just have to use SUM on Employee assignment. Make sure that employee assignment line item in Mod B is list formatted line item on Employee Assignment List.

'Module B'.'Period Quota (USD)'[ SUM: EmployeeAssignment]

Hope that helps,

Misbah

Miz Logix

CommunityMember96641

Hi Misbah

I tried to create a new module equal to adding Employee Assignment list to Module B, but system errored saying it exceeds the model size. I guess there is too much sparity as one employee assignment has to have 1600 lines with Mapping List with items like PSM1, PSM2, ..PSM1600). Any other options? Thank you!

Misbah

Please do not add Sales Territory Quota Mapping dimension in Module C. This should be the dimensionality of Mod C

And then Use this formula

'Module B'.'Period Quota (USD)'[ SUM: Module B.EmployeeAssignment]

Thanks,

Misbah

Miz Logix

CommunityMember96641

Hi Misbah

My last screen is what I tried for Module B, I did not change module C.

The challenge is still how to sum from Sales Territory Quota Mapping List with items like PSM1, PSM2, ..PSM1600 to EmployeeAssignment.

Thanks!

Merlin