Effects of Best and Worst KPI Values on KPI Score

A few months ago I wrote a blog post on how the scores of KPIs are calculated (see article here), this post will expand on that topic and show how the best and worst values can impact your scores.

Recall that the scores of the KPIs are mapped into the standard ranges for the number of KPI bands that you have. Also recall that the band for the highest level ranges from the upper threshold to the best value and the band for the lowest threshold to the worst value.  Let's review a couple of simple examples to see how the scores are affected when best and worst values are adjusted.

Example 1:

KPI Value 105

KPI Target 100

Increasing is Better



Based on the scoring algorithm from the above mentioned post, the score based on these settings is 83.3%.


This score is based off of the best value of 120 - so for values 120 and higher, the score will always reflect 100%, regardless of how high the actual value is.

If we adjust the best value to 200, the score is now 71.2% because the value 105 is much farther away from the best value of 200.


Similarly, if we adjust the best value down to 100, the score is now 100%.


You can see that the stoplight is always green; however, the actual score used when rolling up this KPI into an objective can be significantly different.  For this reason, you should carefully select your best values when working with the thresholds so as to most effectively roll up your KPIs and achieve the best results in your scorecards.

Example 2:

KPI Value 75

KPI Target 100

Increasing is Better



Based on these settings, the KPI score is 31.3%. The value of the KPI is near the threshold 1 setting and very distant form the worst setting so it gets a value very close to 33%.


Now, if we adjust the worst value to 70% - meaning that all KPI values less than 70% of the target should be scored as a zero, we get the following score - 16.7%:


Any KPI value of less than 70 would automatically be scored as 0%.

If we adjust the worst value to 75%, we get a score of 0.


Again, the stoplight is red regardless of how much below the lowest threshold we are, but the score used when rolling this KPI into an objective can be significantly different.

Using this example, if we leave the worst value at 0, we see our objective is yellow:


However, if we change our worst value to 75% and our score goes to 0, we get an objective that is red:



Although it may be easy to accept the defaults for best and worst values when defining KPIs, you should really put some thought into the values and ensure that the value you place in the best/worst fields are the true targets that you want your numbers to be scored against.  It can significantly affect the objective values and determine whether your objectives are reflected as On Target or not.

Alyson Powell Erwin


Comments (3)

  1. Bobby_Kotti says:


    I have two date list filters, one is for Fromdate and another is for Todate. My KPI should get the actual values from the cube for given date range(Between Fromdate and Todate).Can u show me how to create MDX tuple formula for this.

    Thanks in advance.


  2. PinkBunny says:

    Is there a way to set up the KPI scores without setting best/worst values?  For my KPI scores to be effective they need to be calculated only using the actual and target values.  I am tracking cost accounts and the values within the accounts have large ranges and having to enter a fixed amount as best/worst amount does causes accounts not to have the correct indicator.

Skip to main content