Feature progress rollup sample report

Article
2025-04-28

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

This article shows you how to create a stacked bar report to display progress of Features based on completed child User Stories. The report displays the percentage complete by rollup of Story Points for a given set of active Features. You can view similar progress bar charts from your backlog by adding a rollup column. To learn how, see Display rollup progress or totals.

When you create rollup reports using Power BI, you might encounter issues related to nested data in your datasets. Specifically, attempting to pivot a table that contains columns with nested data can result in an error message. For more information, see the section about Handling nested data.

Prerequisites

Category	Requirements
Access levels	- Project member. - At least Basic access.
Permissions	By default, project members have permission to query Analytics and create views. For more information about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Note

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

Sample queries

Feature progress queries the WorkItems entity to get the current state of progress.

Note

To determine available properties for filter or report purposes, see Metadata reference for Azure Boards. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. For more information about the data type of each value, review the metadata provided for the corresponding EntityType.

Review feature progress based on an area path

Power BI query
OData query

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 'Removed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Descendants/any()"
            &"&$select=WorkItemId,Title,Area,Iteration,AssignedTo,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

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
        $filter=WorkItemType eq 'Feature'
            and State ne 'Removed'
            and startswith(Area/AreaPath,'{areapath}')
            and Descendants/any()
        &$select=WorkItemId,Title,Area,Iteration,AssignedTo,WorkItemType,State,AreaSK
        &$expand=Descendants(
            $apply=filter(WorkItemType eq 'User Story')
                /groupby((StateCategory),
                aggregate(StoryPoints with sum as TotalStoryPoints))
            )

Substitution strings and query breakdown

Substitute the following strings with your values. Don't 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 part

Description

$filter=WorkItemType eq 'Feature'

Return Features.

and State ne 'Cut'

Omit Features marked as Cut.

and startswith(Area/AreaPath,'{areapath}')

Return 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()

Filter out any work items that have at least one or "any" descendant. Includes all Features with at least one Child work item. To get all work items with their descendants, even if they don't have any, run a query without the Descendants/any() filter. To omit Features that don't have child User Stories, replace with any(d:d/WorkItemType eq 'User Story').

For all work items with and without descendants:

$filter=endswith(Area/AreaPath,'suffix')
&$select=WorkItemId,Title,WorkItemType,State,Area, Descendants
&$expand=Descendants($select=WorkItemId)

For all work items with at least one descendant:

$filter=endswith(Area/AreaPath, 'suffix')and Descendants/any()
&$select=WorkItemId,Title,WorkItemType,State,Area, Descendants
&$expand=Descendants($select=WorkItemId)

&$select=WorkItemId, Title, WorkItemType, State

Select properties to return.

&$expand=Descendants(

Start of expand Descendants clause

$apply=filter(WorkItemType eq 'User Story')

Filter the descendants. Only include User Stories (omit 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() clause.

Review feature progress for a team

The following query is the same as the one previously used, except it filters by Team Name rather than Area Path.

Power BI query
OData query

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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))
            )

Transform the data in Power Query Editor

The query returns several columns that you need to expand before you can use them to create a report. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. Expand the record to flatten the entity into its fields.

For the Feature Progress report, you need to carry out the following transforms:

Expand the Descendants column into two columns: Descendants.StateCategory and Descendants.TotalStoryPoints
Apply Pivot Column transform on Descendants.StateCategory column to separate out individual State categories
Replace null values in all pivoted columns.
Add a custom column that represents percentage complete. The custom column displays errors if there are any null columns in the pivoted State columns.

To learn how, see Transform Analytics data to generate Power BI reports.

Handling nested data

Before you perform a pivot operation, ensure that your dataset doesn't contain any columns with nested data. Follow these steps:

Identify nested columns: Check your dataset for any columns that might contain lists, records, or tables within a single cell.
Flatten or remove nested data: You can either remove the nested columns or flatten them by expanding into separate columns using the "Expand" feature in Power Query.
Perform the pivot operation: Once you address the nested data, you can proceed with the pivot operation without encountering errors.

Note

In this example, the State values for User Story include Proposed, In Progress, and Completed.

Close the query and apply your changes

Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.

Create the stacked bar chart report

In Power BI, choose Stacked bar chart report under Visualizations.
Add Title to Y-Axis.
Add PercentComplete to X-Axis, right-click, and select Sum.

The example report displays.

Screenshot of Sample Feature Progress stacked bar chart report.

Share via

Feature progress rollup sample report

Prerequisites

Sample queries

Review feature progress based on an area path

Substitution strings and query breakdown

Query breakdown

Review feature progress for a team

Transform the data in Power Query Editor

Handling nested data

Close the query and apply your changes

Create the stacked bar chart report

Related articles

Feedback

Additional resources