Hi all,
I need some help please for data modeling.
I would like to build a dashboard to help the sales team to keep track of their individual earnings on the 2017 Incentive scheme
I have bonus payment matrix, per country, which is defined by “band”. The bands are different for each country
For example, for the UK:
| Tiers | Payout |
|---|
| [1;24] | 50£ |
| [25;49] | 75£ |
| [50;74] | 100 |
| >74 | 125 |
Progress through the band levels is based on cumulative sales volume for the individual through the year
If the individual achieves their quota for a given quarter, they receive a Kicker
- The kicker is a percentage of earnings in that quarter.
- Each person has a different Kicker, but the kicker % will remain the same in each quarter
The targets for the UK for example:
| SalesRep | Country | Year | Q1 | Q2 | Q3 | Q4 |
|---|
| Toto | UK | 2017 | 18 | 25 | 27 | 30 |
| Titi | UK | 2017 | 26 | 28 | 24 | 25 |
| Tata | Uk | 2017 | 28 | 22 | 24 | 28 |
The Kicker for the UK:
| Tiers | Volume | Kicker |
|---|
| 1 | >60 | 20% |
| 2 | [50;60] | 25% |
| 3 | <=30 | 70% |
I have my fact table where i have the volume (#SalesQty) where i have the SalesRep, the country and the salesdate.
I would like to :
- Show progress towards annual target (e.g. a progress bar or thermometer) with the ability to distinguish between different quarters
- Show current quarter sales and target (to make it clear if you will reach your quota for the current Quarter and earn a kicker), and the cumulative sales YTD (affects which band you are earning in)
- Show a breakdown of the users earnings to date
Could you please help me to build my data model, how to link the volume to the bands,and how to calculate the metrics?
Many thanks by advance.
