When viewing scorecards in PerformancePoint you will notice not only an actual and target value for the KPI but also a status indicator. The status indicator is determined by the KPI score, shown below.
The KPI score is a normalized score based on the KPI settings . This score allows the KPI to be properly compared with other KPIs and aggregated into an objective. In the screenshot above, we have four KPIs that roll up to the Increase Revenue objective: Sales Amt, Sales Amt - % Growth PP, Unit Sales and Unit Sales - % Growth PP. The Increase Revenue target status is determined by the scores of the four KPIs comprising this objective.
Several steps are involved when calculating the KPI score. First, we identify the Band by Value, which is the value that we will ultimately map into the threshold ranges of the KPI to determine a score. Second, we calculate the correct normalized value of the Band by Value so that we can effectively roll up the scores across multiple KPIs into an objective score that makes sense. Outlined below are the steps for calculating the normalized KPI score.
The BBV is calculated differently depending on the banding method and scoring pattern chosen.
Scoring Pattern: Increasing is Better, Decreasing is Better, Closer to Target is Better
Banding Method: Band by Numeric Value of Actual
BBV = value of the actual
Note: Our algorithms assume a ratio is provided for the actual value. Although it works with non-ratios, there may be some corner cases where it will fail.
Banding Method: Band by Stated Score
BBV = query result
*Query result is the result of the MDX expression entered during threshold setup where banding method and scoring pattern are chosen
Scoring Pattern: Increasing is Better, Closer to Target is Better
Banding Method: Band by Normalized Value of Actual to Target
BBV = (Actual Value - Worst)/(Target Value - Worst)
*Worst value is the value entered in the Threshold wizard
Scoring Pattern: Decreasing is Better
BBV = 1 - ((Actual Value - Worst)/(Target Value - Worst) )
The In-Band Value (IBV) is a 1-based value that determines in which threshold range the BBV lies. Range 1 is always the bottom range in the threshold settings; range 2 is the range immediately above the bottom range, and so on. If you are using a three-state indicator there are three ranges, a four-state indicator has four ranges. The following screenshot shows both the BBV and IBV for the three scoring patterns:
Click on the below image to see the entire set of values.
The Normalized Band by Value (NBV) is the normalized score shown in the scorecard. This value is calculated to equalize the KPI scores so they can be accurately aggregated for objective KPIs. Consider the following two KPIs:
The BBV value for the first KPI is .6 (with a worst value of 50, this is (80-50)/(100-50)). The BBV value for the second KPI is 4. Because these KPIs have a different scale and threshold, we cannot simply combine them together to achieve a rational aggregate value. Instead, we must calculate normalized values that can be proportionally mapped to the same range. For a three-state indicator, this range is 100/67/33/0; for a four-state indicator, this range is 100/75/50/25/0.
In the first example above, we map the KPI's threshold ranges of 120/80/40/0 to the standard scale of 100/67/33/0 and determine a normalized (NBV) value to represent the raw (BBV) value on this standard scale. In the second example, we map the KPI's threshold ranges of 10/5/2/0 to the standard scale of 100/67/33/0 and determine a normalized (NBV) value to represent the raw (BBV) value on this standard scale.
Using the simple example shown in the first KPI above, we convert .6 to 60%. Sixty percent is at the exact midpoint of its threshold range (120-0), so it's mapped to 50% on the standard range (100-0). In the sections below, we will provide the mathematical equations to illustrate this concept in greater detail.
NBV is calculated using the following formula:
NBV = ((Upper - Lower) * (d/T)) + Lower
With the following values:
Upper = In-Band/# of Bands
Lower = (In-Band-1)/# of Bands
d= (BBV - lower band threshold)
T= (upper band threshold - lower band threshold)
Using the following KPI settings:
Scoring Pattern: Increasing is Better
Banding Method: Band by normalized value of Actual/Target
Worst Value: 50
And the following values:
BBV = (80-50)/(100-50) = .6
In-Band = 2
Upper = 2/3 = .67
Lower = 1/3 = .33
d = (.6 - .4) = .2
T = (.8 - .4) = .4
The calculation is as follows:
NBV = ((.67-.33)*(.2/.4)) + .33 = .50
And the score is shown in the scorecard below:
Let's walk through another example with a KPI banded by actual:
With the following settings:
Banding Method: Band by numeric value of Actual
Worst Value: 0
Using the following values:
BBV = 4
d = (4-2) = 2
T = (5 - 2) = 3
NBV = ((.67-.33)*(2/3)) + .33 = .556
The NBV calculation for closer to target KPIs is identical to the NBV for Increasing is Better KPIs, with one exception. Once we have the NBV from the above Increasing is Better calculation, we make the following adjustment:
If In-Band < (# of Bands/2) then
NBV = NBV * 2
NBV = 2 * (1 - NBV)
Let's look at an example for this calculation.
Scoring Pattern: Closer to Target is Better
BBV = -1
Upper = 2/6 = .33
Lower = 1/6 = .17
d = (-1-(-2)) = 1
T = (0 - (-2)) = 2
NBV = ((.33-.17)*(1/2)) + .17 = .25
In-Band < (6/2) so we multiply NBV by 2 to yield .50
Let's walk through another example where the actual is 6. Using the following values:
BBV = 6
Upper = 6/6 = 1
Lower = 5/6 = .8333
d = (6-5) = 1
NBV = ((1-.8333)*(1/5)) + .83 = .866
In-Band >= 3 so we calculate NBV as:
NBV = 2(1-.866) = .2668
Although the NBV calculation for Decreasing is Better KPIs is identical to the NBV calculation for Increasing is Better KPIs, the BBV is calculated differently.
Worst Value: 200 (as entered in threshold settings)
Using the following values (note the BBV calculation):
BBV = 1 - ((80-200)/0-200)) = .4
In-Band = 3
Upper = 3/3 = 1
Lower = 2/3 = .667
d = (.4 - .4) = 0
T = (0 - .4) = -.4
NBV = ((1-.667)*(0/-.4)) + .667 = .667
Let's walk through another example where the actual is 100. With the following settings:
BBV = 1 - ((100-200)/0-200)) = .5
Upper = 2/3 = .667
Lower = 1/3 = .333
d = (.5- .8) = -.3
T = (.4 - .8) = -.4
NBV = ((.667-.333)*(-.3/-.4)) + .333 = .583
Scoring Roll-Up to Objectives
Once each KPI has the score determined the objective score is then calculated. The objective score is a simple average calculation of the KPIs underneath the objective (unless weighting has been applied to one or more of the KPIs, in which case the weighting is factored into the average). The first screenshot in this post shows an objective KPI with the average score.
Look for future blog topics on how the threshold settings and worst values can affect your scores.
Alyson Powell Erwin (email@example.com)