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?
