Share via

Creating Complex KPIs with Calculated Metrics

  • Article

Many times, the metrics that make up your key performance indicators are not simple values from a data source. In PerformancePoint Server 2007 you could create two kinds of KPI metrics: Simple single value metrics from any supported data source or Complex multiple value metrics from a single Analysis Services data source using MDX.

When a user needed a calculation as simple as ‘Sales per Square Foot,’ both the sales value and the square footage values had to be available in an Analysis Services cube, and the calculation had to be written in complex MDX statements.

Luckily this is no longer the case in PerformancePoint Services for SharePoint 2010. This release we have introduced a new feature called “Calculated Metrics.” Calculated Metrics are powerful for several reasons:

  • Can use multiple data sources in the same formula.
  • Can use different data source types in the same formula.
  • Is expressed in simple Excel-like formulas.
  • Supports logic flow.

The possibilities are endless.

image

I quick view at the Calculated Metric dialog shows how simple the process really is. The grid at the top maps data sources to common names you can use in your formula. The Formula box contains the formula you have written and even uses simple code highlighting techniques to help you quickly understand what’s being calculated.

The formulas support all simple math operations as well as functions like MIN, MAX, SUM, AVG and ABS. You can create IF statements in your formulas to create logical operations and you can even use NULL to test for data or intentionally return a null result.

This topic gets much deeper and we will post examples of complex usage in the coming weeks. Until then, when you get your hands on the beta in November, use this feature and provide feedback. Enjoy!

Jason Burns, Program Manager
PerformancePoint Services

Comments

  • Anonymous
    July 22, 2010
    Still waiting for a follow up on this posting "This topic gets much deeper and we will post examples of complex usage in the coming weeks."  Is someone planning on doing an additional posting on this topic at all?

  • Anonymous
    September 09, 2010
    Is there anyway to create calcualted members in Analytic Chart or Grid? We are using ProClarity and we use lots of calculated members in our reports.

  • Anonymous
    October 26, 2010
    Can we have targets drill down bye months, quarters? from Year

  • Anonymous
    February 17, 2011
    It is now February 2011. In October 2009, this post described exciting features of the Calculated Metrics of Sharepoint 2010: <blockquote>You can create IF statements in your formulas to create logical operations and you can even use NULL to test for data or intentionally return a null result.</blockquote> The article was brief but assured that <blockquote>we will post examples of complex usage in the coming weeks.</blockquote> Was there every any follow-up, if not here, then on another MSDN blog? Since there is (or was) obvious interest, either the promised information or a follow-up of some sort should be provided as nearly 2.5 years have elapsed. Thank you!