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)

Comments

• Anonymous
  January 09, 2008
  The Band by Stated Score Band banding method allows a user to band an indicator by a score that is sourced

• Anonymous
  January 09, 2008
  on what basis the percentage values are calculated?

• Anonymous
  January 10, 2008
  What if one wants to expose the actual Variance value as a column in the scorecard (as it is in the spreadsheet in this article)? How would you approach this?

• Anonymous
  January 10, 2008
  To see how scores are calculated refer to the product documentation, under "Walkthrough: Calculate normalized KPI scores" If you want to expose the variance value, add a new target field to your KPI and point it to the variance column in your spreadsheet.

• Anonymous
  January 07, 2009
  It frequently comes up that you may want to show a date in your scorecard, either as an actual value,

• Anonymous
  May 20, 2009
  to show the varience field as a column you just have to add another target to KPI by clicking on green color "New target" button(visible in pic 3 from top in this blog) and do data mapping for this new target to "Varience" field and rest the same if you wanna show indicator along with it.

• Anonymous
  April 01, 2015
  This is a very helpful blog.But pictures are not loading in any browsers.