Condition Report By Pivot table

[faizee]

CLIENT SALES DATA
CLIENTS	DATE	SALES AMOUNT
CLIENT 4	10-Feb-24	819
CLIENT 4	10-Feb-24	326
CLIENT 4	10-Feb-24	952
CLIENT 3	10-Feb-24	210
CLIENT 3	10-Feb-24	820
CLIENT 3	10-Feb-24	809
CLIENT 3	10-Feb-24	298
CLIENT 2	10-Feb-24	728
CLIENT 2	10-Feb-24	772
CLIENT 2	10-Feb-24	468
CLIENT 2	10-Feb-24	398
CLIENT 1	10-Feb-24	487
CLIENT 1	10-Feb-24	426
CLIENT 1	10-Feb-24	310
CLIENT 4	05-Mar-24	950
CLIENT 4	05-Mar-24	519
CLIENT 4	05-Mar-24	855
CLIENT 3	05-Mar-24	489
CLIENT 3	05-Mar-24	722
CLIENT 3	05-Mar-24	523
CLIENT 3	05-Mar-24	814
CLIENT 2	05-Mar-24	785
CLIENT 2	05-Mar-24	267
CLIENT 2	05-Mar-24	789
CLIENT 2	05-Mar-24	488
CLIENT 1	05-Mar-24	137
CLIENT 1	05-Mar-24	518
CLIENT 1	05-Mar-24	795
CLIENT 4	01-Apr-24	875
CLIENT 4	01-Apr-24	578
CLIENT 4	01-Apr-24	748
CLIENT 3	01-Apr-24	494
CLIENT 3	01-Apr-24	914
CLIENT 3	01-Apr-24	475
CLIENT 3	01-Apr-24	806
CLIENT 2	01-Apr-24	815
CLIENT 2	01-Apr-24	739
CLIENT 2	01-Apr-24	624
CLIENT 2	01-Apr-24	559
CLIENT 1	01-Apr-24	788
CLIENT 1	01-Apr-24	387
CLIENT 1	01-Apr-24	445

CLIENT MASTER DATA
CLIENTS	REPRESENTATIVE	SUPPORT BY
CLIENT 1	Mr. A	Mr. B
CLIENT 2	Mr. A	Mr. A
CLIENT 3	Mr. B	Mr. A
CLIENT 4	Mr. C	Mr. C
REPRESENTATIVE MASTER DATSA
NAME	% OF SALES
Mr. A	2%
Mr. B	5%
Mr. C	1%
1st Pivot table report by clients sales with mentioning relevant "Representative" and "support by" in columns
CLIENTS	Representative	Support By	Feb	Mar	Apr
CLIENT 1	Mr. A	Mr. B	1223	1450	1620
CLIENT 2	Mr. A	Mr. A	2366	2329	2737
CLIENT 3	Mr. B	Mr. A	2137	2548	2689
CLIENT 4	Mr. C	Mr. C	2097	2324	2201
2nd pivot table report, "REPRESENTAVE" will get 75% and "SUPPORT BY" will get 25% of sales x Representative % of sales
REPRESENTATIVE	Feb	Mar	Apr
Mr. A	64.5
Mr. B	95.3
Mr. C
for example? Mr A client1 got sales Feb 1223 + client2 got sales 2366 =3589
3589 x 75% = 2691
2691 x Mr A % (2%) = 53.8
where Mr A as "support by" of client 3 got feb sales 2137
2137 x 25% = 534
534 x Mr A % (2%) =10.7
so Mr A , feb total commision is (53.8 + 10.7)= 64.5

 

[faizee]

anyone,?
please support