1 table, Structure, with Year/Month, BrickCode, Product, Value
1 table, Data, with BrickCode, BrickName, New_Brick, SalesRep_Surname
As you can see in the attached files the New_Brick is defined only for some BrickName and not all bricks related to a SalesRep are groupped in the New_Brick. i.e SalesRep = Rogers as 13 Bricks but only 4 in the New_Brick.
What i need is to distribute the full amount of New_Brick equally to all SalesRep in the New_Brick group.
Example:
For istance full amount of MILANO (New_Brick) is 1.200 where Rogers makes 400 €, Lucas makes 700 € and Coppola makes 100 €.
I need to have the 1.200 € divided equally to the 3 reps: Rogers 400€, Lucas 400 € and Coppola 400 € but in fact they have different number of bricks: Rogers as 4 bricks, Lucas and Coppola 8.
After some test I have defined that I need to recalculate the amount made for each single line in the Data table where:
If New_Brick is null I use Value, if is not null (Milano, Torino, Napoli) I need to divede the full amount for New_Brick (1200) by the number of reps in this macro area (3) and divide the result by the number of records existing in Data table.
i.e if Rogers as 4 bricks in the New_Brick Milano I need to count how many records I have in the table Dati for these bricks (100). Now the formula is 1200 / 3 = 400 / 100 = 4.
Lucas as 200 lines so the formula is 1200 / 3 = 400 / 200 = 2 and Coppola as 150 lines so 1200 / 3 = 400 / 50 = 8.
Finally I have that each line of Rogers as a "new" value of 4, Lucas of 2 and Coppola 8. The full amount is the same but I have redistributed the values equally to the reps in the Milano area.
