Re: SUM function usage on different dimensionality
The SUM and LOOKUP formulas are used to map dimensions between modules where a direct link does not exist.
Therefore, in order to successfully reproduce the formula in other modules it must contain enough mapping definitions in order to establish the required links.
Example - if you are using SUM to aggregate hours by BUs and employees in a target module where the source contains just the BU dimension you must be map in the target module formula how each employee relates to each BU. Say you have an employee dimension in the target in addition to BUs which is not present in the source you must specify how employees are linked to each BU. This can be achieved by using a mapping module with the employee dimension and a LIST FORMATTED LINE ITEM of the BUs. Select a BU for each Employee and use SUM to build the link between the target and source modules.
The SUM would look something like this;
= Module one.Hours[SUM: Mapping.Employees]
Module one is the source contain details of the hours by BU Mapping is the mapping module telling the target which BUs in the source relate to which employees in the target.
SUM is used when you have many aggregating into one - as above
LOOKUP is used when aggregating one into many - which employee belongs to which BU
I hope this helps to clear the mist that may have formed from all the other comments.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA