Band by Stated Score

The Band by Stated Score Band banding method allows a user to band an indicator by a score that is sourced by a back-end data source (either in a field, or calculated with an MDX expression), as opposed to banding by the normalized score calculated by PerformancePoint. Band by Stated Score is useful if target values fluctuate over time. As long as the target values are stored in the same back-end data source as the actual values, stated score can allow us to have fluctuating targets without having to manually update scoring patterns as targets change.

This example will show how to use Band by Stated Score to meet the following scenaro:

· "Days to Resolve" metric

· Decreasing values are better

· "Not to exceed" value of 8 days, but this value changes over time

· Banding requirements - more than one day over the "not to exceed" target is red, within one day of the target is yellow, and more than one day under the target is green

NOTE: This article assumes basic knowledge of the PerformancePoint Dashboard Designer and KPI thresholds and scoring patterns.

Let's begin with a sample Excel worksheet data source. The worksheet, shown below, contains three rows of data. The rows include date, actual, and target.

The score that we'd like to base the banding on is "actual minus target". If this were an Analysis Services data source, we could write an MDX expression directly into the data mapping for the KPI threshold. But, since this is Excel, we need to add the calculation to the spreadsheet. To do this, we'll simply add a column to the spreadsheet titled "Variance" and write an Excel formula to subtract Actual from Target. The finished spreadsheet is shown below.

The next step is to create an Excel 2007 data source in PerformancePoint Dashboard Designer. Once the data source is created, import the worksheet referenced above. Verify that all numeric columns are marked as "Fact" and that the Date column is marked as a dimension > time dimension.

Next, build the KPI from the Excel data source, with these data mappings:

1. Actual - select the "actual" measure

2. Target - select the "target" measure

At this point, the KPI should look like the below screenshot.

Now set the scoring pattern and indicator. For scoring pattern, select "Decreasing is Better." For banding method, select "Band by stated score (advanced)." For this example, we'll choose a three-state stoplight indicator. The next configuration option is "Specify Data Mapping." Select the Excel data source, and select the "Variance" measure as shown below.

At this point, we've configured the scoring pattern and indicators. We can now configure the thresholds. Since we're using stated score banding, the thresholds will be compared to the value specified in the previous step, which is "Variance." The threshold we want are as follows:

· Green: more than one day under the target

· Yellow: within one day of the target

· Red: more than one day over the target

The thresholds are then configured as shown below:

NOTE: arbitrarily large numbers were used for "best" and "worst"

Now we can place the KPI on a scorecard. For this example, we've placed the KPI on the scorecard rows, and the three time periods from the spreadsheet on the columns.

As expected, the icon for "2007-01" is red, as we exceeded target by two days. The icon for "2007-02" is yellow, as we came within one day of target. The icon for "2007-03" is green, as we were three days under target.

For reference purposes, the normalized scores are displayed to the right of the icons. Note that the score appears to calculate properly - and that this is the score that will be used for roll up scores in an objective KPI.

Questions/feedback? Contact Rex Parker (rexpark@microsoft.com)