Bottom-up Summary based on unique list members
I am aggregating data in a module with three dimensions + Time:
M2, which has M1 as a parent and corresponds to models
R1, which is a list of roles
E3, which is a list of employees and has E2 Team and E1 Group as parents.
I am mapping my list of employees to their Roles which is why I have E3 as a dimension but I need to aggregate from E2 level so E3 won't be used for reporting.
I have the raw data but what actually matters in this module is whether there is data for E2 Team in each role, so I would know for example that in Team 1 there are 3 employees with Role X that have data in Dashboard 1 from M2, I have a line item that writes 1 if there is data or 0 if there is none to count matches here.
I need to sum up the data per role for each combination for M2/M1 and E2/E1 and here is where my problems start: it should be summarized a bit differently than just a sum for each level, below is an example of my issue.
In Model 1 I have 20 dashboards, in Team 1 I have 5 employees.
I am checking Role X (R1) within Team 1 (E2) for Dashboard 1 (M2) I see that 3 employees have data in that dashboard.
I am checking Role X (R1) within Team 1 (E2) for Dashboard 2 (M2) I see that 2 employees have data in that dashboard.
I do that check for each dashboard here, at the M2 level it is fine but when I go up at the M1 level the sum is done the count gets summed to around 40 which of course isn't possible at the R1/E2/M1 level since Team 1 has 5 employees.
Is there a way to sum only unique employees here ? Or should I split the module, using a different approach ? The end goal is that when going from M2 to M1 level is have the sum of unique employees with that Role in the Model, at each E2 and E1 level, not summing every count here. The Summary set as Max didn't work here for me as the bottom level is E3 and everything is 1 for this line item.
Please let me know what I am doing wrong here and if you have tips to achieve that, that would be great !