after going on hiatus for quite a long time I’m back with my favorite issue: The correct aggregation along diamond-shaped hierarchies.
So far I was thinking that MS Analysis would calculate the right values by using the function “distinct sum”.
As far as I understand this function it sums up all distinct values along a hierarchy. IMO this is not the right approach to sum up all values of all distinct members along a hierarchy.
Let’s modify our example data a bit:
On the right you can see a part of the sales force of a financial services company.
C1-C4 is a subset of the clients, R1 and R2 are two sales reps and M1 is a regional manager.
For each client, the measure “a” is given, which means the yearly amount of money available to the client for investment. In order to calculate the investment potential for each node in the hierarchy, “a” has to be summed up.
It is typical for financial services companies, that each rep is expected to fully exploit a client’s potential. That means, that it is no option to use weighting factors to share the client’s potential between reps. As you can see on the right, C2 is assigned to two reps, whereas C2’s full investment potential has to be assigned to R1 and to R2 respectively.
For M1 each client’s potential must be taken into account only once.
To have something to work with, I give you the investment potentials for each client:
- C1: 7.000
- C2: 7.000
- C3: 8.000
- C4: 4.000
Here are the expected results of the aggregation:
- R1: 14.000
- R2: 19.000
- M1: 26.000
IMO, “distinct sum” would deliver the following values, which are NOT correct:
- R1: 7.000
- R2: 19.000
- M1: 19.000
I would highly appreciate to receive further comments by MSAS gurus . Maybe I’m all wrong and the “distinct sum” function would do the job perfectly.