KPI to Score

Dec 2019
2
0
England
I have taken over the responsibility of a task (target dashboard) which has a maths situation I'm not skilled enough to work out.
The situation appears like this: For KPI #1, if the reported value is 75% or less, they score 0.01. If the value is 80% they score 6.67 and if they score 85% or more the score is 10. Basically we are aiming to get 6.67+ on all KPI's.

Now I'm struggling how to code this (using SQL but just need to understand the maths logic at least) so I can measure the score of what ever value they enter. How do I work out what the score is if they enter 77% for example, or 83%?

Apologies if this is really basic. Been a very long time since I've had to do anything more than a +/*or-.

Thanks for any help.
ps. I don't know why they don't just measure % towards target. Seems easier to me but as it is, I have to continue with the same method for now.
 

mathman

Forum Staff
May 2007
6,889
759
It looks linear interpolation in two pieces Between 75% and 80%, $0.01+\frac{x-75}{5}\times 6.66$. Between 80% and 85%, $6.67+\frac{x-80}{5}\times 3.33$.
 
Jun 2019
493
260
USA
Based on the scoring description, it sounds like it was based on a KPI that was designed to only be increments of 5 % (for example, number of questions right out of 20).
If that's still the case, all you need is an if statement.

If it is possible to get reported values of 77 % or 83 %, you should probably check with your bosses what they want to do: Make the score smoother by doing linear interpolation like mathman suggested, or keep it as sharp cutoffs. In the latter case, you just need to determine the cut-offs (does a score of 6.67 start at 75.01 %, or 79.99 %, or 80.00 %, etc. -- and similarly for the upper cut-off).
 
Dec 2019
2
0
England
It looks linear interpolation in two pieces Between 75% and 80%, $0.01+\frac{x-75}{5}\times 6.66$. Between 80% and 85%, $6.67+\frac{x-80}{5}\times 3.33$.
This seems to be really close to what I am looking for. With a bit of changes here and there due to different boundaries for each KPI, it provides a close scoring (not exact). Hopefully something I can work with to produce a finished article.

thanks for the replies and help. greatly appreciated.