Create and Compare a Metric from Other Metrics

Peter Doyle
By | Updated September 5, 2017 | 3 min read
project metrics

As you already know, BrightWork Metrics are used to calculate both the Performance and Progress of a Project.

Sometimes you may need to create a Metric that is a calculation from other Metrics in a Project.

In this example, I will perform a calculation to compare metrics between the Actual Work of a Project against the Planned Work. The formula used is % Actual Work = Actual Work / Planned Work. 

The steps outlined below may also be adapted for other metrics.

 

Compare Metrics

  1. Create a Custom Report definition that can query the Project Metrics list and returns just the metrics that you want to use in the calculation. Here is one that you can reuse.
  2. Add CAML query to return the desired Metrics, in this case Planned Work and Actual Work

<Where>

<Or>

<Eq>

<FieldRef Name=”Title” />

<Value Type=”Text”>Planned Work</Value>

</Eq>

<Eq>

<FieldRef Name=”Title” />

<Value Type=”Text”>Actual Work</Value>

</Eq>

</Or>

</Where>

 

  1. Add two additional Columns to return the Metric values as separate Columns using Expressions. Metrics Calc is the raw XML on how the Columns are defined.
    •  <NumberColumn name=”PlannedWork” caption=” Planned Work” defaultValue=”0″ numberOfDecimals=”NoDecimal” displayAsPercentage=”false” expression=”iif([Title]=’Planned Work’,[bwMetricNumberValue],0)” />

     

    • <NumberColumn name=”ActualWork” caption=” Actual Work” defaultValue=”0″ numberOfDecimals=”OneDecimal” displayAsPercentage=”false” expression=”iif([Title]=’ Actual Work’,[bwMetricNumberValue],0)” />

     

  2. Add two more additional Columns that would sum them up in order to calculate them against each other:
    • <NumberColumn name=”ActualWorkSum” caption=” Actual Work Sum” defaultValue=”0″ numberOfDecimals=”OneDecimal” displayAsPercentage=”false” expression=”SUM(ActualWorkSum)” />

     

    • <NumberColumn name=”PlannedWorkSum” caption=” Planned Work Sum” defaultValue=”0″ numberOfDecimals=”OneDecimal” displayAsPercentage=”false” expression=”SUM(PlannedWork)” />

     

  3. Finally create a 5th Column to divide the SUM Columns by each other
    • <NumberColumn name=”WorkCalc” caption=”Work Calculation” defaultValue=”0″ numberOfDecimals=”OneDecimal” displayAsPercentage=”true” expression=”iif([ActualWorkSum]=0,0, PlannedWorkSum/ActualWorkSum)” />

     

  4. When you run the report the following information is returned. As you can see, we needed to create the two additional SUM Columns in order to calculate one Metric with another: Compare Metrics
  5. Finally, create a 3rd Metric to average the Calc Column and it will return the Actual Work as a % of the Planned Work. Display Comparison Metrics

 

 

Peter Doyle
Peter Doyle

BrightWork Project Management Consultant

Don't forget to share this post!