Multiple Salesperson to Client

[coolwaters]

Hello,

Am working on data where at times multiple sales persons are assigned to a single client. Thus where more then one sales person is assigned to a client then the sales generated from that client is to be apportioned equally amongts the number of sales persons assigned.

SALES:
LOAD
CLIENT,
SALES
INLINE [
     CLIENT, SALES
     C001, 100
     C002, 100
     C003, 100
     C004, 100
     C005, 100
     C006, 100
     C007, 100
];

SALESPERSON:
LOAD
SALESPERSON,
CLIENT
INLINE [
     SALESPERSON, CLIENT
     SP01, C001
     SP01, C002
     SP01, C003
     SP02, C003
     SP02, C004
     SP03, C005
     SP03, C006
     SP04, C006
     SP05, C006
     SP05, C007
];

As per the data above, the report should be :

SALES PERSON	SALES
SP01	250
SP02	150
SP03	133.33
SP04	33.33
SP05	133.33

Need to know the best way to achive this.

Thanks

[sunny_talwar · Accepted solution]

Try this

Dimension

SALESPERSON

Expression

=Sum(Aggr(Sum(SALES)/Count(DISTINCT TOTAL <CLIENT> SALESPERSON), CLIENT, SALESPERSON))

[coolwaters]

Thanks Sunny, as always

It works. Appreciate all your help.