Feature progress rollup sample report

Azure DevOps Services | Azure DevOps Server 2019

This article shows you how to display the percentage complete by rollup of Story Points for a given set of active Features. An example is shown in the following image.

Sample - Feature Progress - Report

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Sample queries

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'Feature' "
            &"and State ne 'Cut' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Descendants/any() "
        &"&$select=WorkItemId,Title,WorkItemType,State,AreaSK "
        &"&$expand=Descendants( "
            &"$apply=filter(WorkItemType eq 'User Story') "
                &"/groupby((StateCategory), "
                &"aggregate(StoryPoints with sum as TotalStoryPoints)) "
            &")  "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Substitution strings

Each query contains the following strings that you must substitute with your values. Do not include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {areapath} - Your Area Path. Example format: Project\Level1\Level2

Query breakdown

The following table describes each part of the query.

Query partDescription
$filter=WorkItemType eq 'Feature'Return Features.
and State ne 'Cut'Omit Features marked as Cut.
and startswith(Area/AreaPath,'{areapath}')Work items under a specific Area Path. Replacing with Area/AreaPath eq '{areapath}' returns items at a specific Area Path.
To filter by Team Name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'.
and Descendants/any()Include all Features, even those with no User Stories. Replace with "any(d:d/WorkItemType eq 'User Story')" to omit Features that don't have child User Stories.
&$select=WorkItemId, Title, WorkItemType, StateSelect fields to return.
&$expand=Descendants(Expand Descendants.
$apply=filter(WorkItemType eq 'User Story')Filters the descendants. Only include User Stories (omits Tasks and Bugs).
/groupby((StateCategory), Group the rollup by StateCategory. For more information on State Categories see How workflow states and state categories are used in Backlogs and Boards.
aggregate(StoryPoints with sum as TotalStoryPoints))Aggregate sum of Story Points.
)Close Descendants().

Power BI transforms

Expand Area, Iteration, AssignedTo columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

  1. Choose the expand button.

    Power BI + OData - expanding an entity column

  2. Select the fields to flatten.

    Power BI + OData - expanding an entity column

  3. The table now contains entity field(s).

    Power BI + OData - expanding an entity column

  4. Repeat steps 1 through 3 for all fields representing entities: Area, Iteration, AssignedTo.

Expand Descendants column

The Descendants column contains a table with two fields: State and TotalStoryPoints. You need to expand it.

  1. Select the expand button on the Descendants column.

    Power BI + OData - expanding an entity column

  2. Check all the fields and choose OK.

    Power BI + OData - expanding an entity column

  3. Table now contains rollup fields.

    Power BI + OData - expanding an entity column

Pivot Descendants.StateCategory column

  1. Click the Descendants.StateCategory column header to select it.

  2. Select Transform menu

  3. Select Pivot Column

  4. For Values select "Descendants.TotalStoryPoints"

  5. Press OK. Power BI creates a column for every StateCategory value.

    Power BI + OData - expanding an entity column

Replace Nulls in the pivoted columns

Some of the new Pivoted StateCategory columns will have null values. For easier reporting, replace the nulls with zeroes.

  1. Select the column by clicking the column header.

  2. Select the Transform menu.

  3. Select Replace Values. The Replace Values dialog appears.

  4. Enter "null" in Value to Find.

  5. Enter "0" in Replace With.

    Power BI - replace nulls with zeros

  6. Choose OK.

Repeat for every Pivoted StateCategory column.

Create a Percentage Complete computed column

  1. Select Add Column menu.

  2. Select Custom Column.

  3. Enter "PercentComplete" for New column name.

  4. Enter the following in Custom column formula.

    = ([Proposed]+[InProgress]+[Resolved])/([Proposed]+[InProgress]+[Resolved]+[Completed])
    

    Note

    It is possible you won't have a Resolved column, if the work items don't have States mapped to the Resolved State Category. If so, then omit "[Resolved]" in the above formula.

  5. Press OK.

  6. Select Transform menu.

  7. Select Data Type and select Percentage.

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  3. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Sample - Feature Progress - Fields

For a simple report, perform the following steps:

  1. Select the "PercentageComplete" field in the Fields list.
  2. Select Modeling menu.
  3. Select Format and choose Percentage.
  4. Select Power BI Visualization Clustered bar chart.
  5. Add the field "Title" to Axis.
  6. Add the field "PercentageComplete" to Values.
    • Right click PercentageComplete and select Sum.

The example report displays.

Sample - Feature Progress - Report

Pull in data from multiple teams

Oftentimes, you want to aggregate and compare data from multiple teams. If you are pulling in items from multiple teams, consider adding a Team slicer to your report. A Team slicer allows you to filter the existing report by Team Name.

Additional queries

You can use the following additional queries to create different but similar reports using the same steps defined previously in this article.

Filter by Teams, rather than Area Path

This query is the same as the one used above, except it filters by Team Name rather than Area Path.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'Feature' "
            &"and State ne 'Cut' "
            &"and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) "
            &"and Descendants/any() "
        &"&$select=WorkItemId,Title,WorkItemType,State,AreaSK "
        &"&$expand=Descendants( "
            &"$apply=filter(WorkItemType eq 'User Story') "
                &"/groupby((StateCategory), "
                &"aggregate(StoryPoints with sum as TotalStoryPoints)) "
            &")  "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Full list of sample reports