Hi team,
I have a table imported from a folder with monthly reports for all customer and all their products, and a simple messure to total monthly revenue (sum monthly revenue, nothing fancy!) this is a large report with neumorous customers and their allocated Account Manager
I need to replicate the below calculations and then apply some calculations for a bonus;
| Monthly Rev | Mar-20 | Apr-20 | May-20 | Jun-20 |
| Rev Total | £40,000 | £40,500 | £40,750 | £41,000 |
| Cumualtive Target | | £40,000 | £80,000 | £120,000 |
| Cumualtive Actual | | £40,500 | £81,250 | £122,250 |
| +/- Target % | | 101% | 102% | 102% |
If "Target" is positive (above 100%), pay sales staff a set figure - i.e. they have a bonus for keeping revenue up in their accounts. they get £0 if it is below 100%
Therefore I have a seperate table with the bonus figures;
| Account Manager | Retention Bonus |
| John Smith | £8,000 |
| Joe Bloggs | £5,000 |
| Suzzie Smith | £12,500 |
I dont need to use the %'s possibly just the cumulative figures it doesnt matter.
The revenue table is refreshed monthly but bonus is paid quarterly. Therefore the messure might need to be fixed, which is fine. I.e. I need to be showing sales people whether they are on track each month of a quarter.
so, calculate cumulative total, compare to target, if greater for sales persons account return Rentention Bonus, else £0.
Hopefully I have explained that well.
Thanks in advance
James
