Skip to content

sample-boards-featureprogress.md

Latest commit

 

History

History
283 lines (230 loc) · 10.8 KB

sample-boards-featureprogress.md

File metadata and controls

283 lines (230 loc) · 10.8 KB
title Feature Progress rollup sample Power BI report
titleSuffix Azure DevOps
description Learn how to generate feature progress rollup by Story Points Power BI report.
ms.subservice azure-devops-analytics
ai-usage ai-assisted
ms.author chcomley
ms.custom powerbisample, engagement-fy23
author chcomley
ms.topic sample
monikerRange <=azure-devops
ms.date 04/28/2025

Feature progress rollup sample report

[!INCLUDE version-lt-eq-azure-devops]

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

[!INCLUDE prerequisites-simple]

[!INCLUDE temp]

Sample queries

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

[!INCLUDE temp]

Review feature progress based on an area path

Power BI query

[!INCLUDE temp]

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

OData query

[!INCLUDE temp]

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

[!INCLUDE temp]

  • {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.

:::row::: :::column span="1"::: Query part
:::column-end::: :::column span="1"::: Description :::column-end::: :::row-end:::

:::row::: :::column span="1"::: $filter=WorkItemType eq 'Feature' :::column-end::: :::column span="1"::: Return Features. :::column-end::: :::row-end::: :::row::: :::column span="1"::: and State ne 'Cut' :::column-end::: :::column span="1"::: Omit Features marked as Cut. :::column-end::: :::row-end::: :::row::: :::column span="1"::: and startswith(Area/AreaPath,'{areapath}')
:::column-end::: :::column span="1"::: 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})'. :::column-end::: :::row-end::: :::row::: :::column span="1"::: and Descendants/any() :::column-end::: :::column span="1"::: 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)
  ```

:::column-end::: :::row-end::: :::row::: :::column span="1"::: &$select=WorkItemId, Title, WorkItemType, State
:::column-end::: :::column span="1"::: Select properties to return. :::column-end::: :::row-end::: :::row::: :::column span="1"::: &$expand=Descendants(
:::column-end::: :::column span="1"::: Start of expand Descendants clause :::column-end::: :::row-end::: :::row::: :::column span="1"::: $apply=filter(WorkItemType eq 'User Story')
:::column-end::: :::column span="1"::: Filter the descendants. Only include User Stories (omit Tasks and Bugs). :::column-end::: :::row-end::: :::row::: :::column span="1"::: /groupby((StateCategory),
:::column-end::: :::column span="1"::: Group the rollup by StateCategory. For more information on State Categories, see How workflow states and state categories are used in Backlogs and Boards. :::column-end::: :::row-end::: :::row::: :::column span="1"::: aggregate(StoryPoints with sum as TotalStoryPoints))
:::column-end::: :::column span="1"::: Aggregate sum of Story Points. :::column-end::: :::row-end::: :::row::: :::column span="1"::: )
:::column-end::: :::column span="1"::: Close Descendants() clause. :::column-end::: :::row-end:::

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

[!INCLUDE temp]

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

OData query

[!INCLUDE temp]

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:

  1. Identify nested columns: Check your dataset for any columns that might contain lists, records, or tables within a single cell.
  2. 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.
  3. 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.

[!INCLUDE temp]

Create the stacked bar chart report

  1. In Power BI, choose Stacked bar chart report under Visualizations.

    :::image type="content" source="media/reports-boards/feature-progress-visualizations.png" alt-text="Screenshot of Power BI Visualizations and Fields selections for Feature Progress stacked bar chart report. ":::

  2. Add Title to Y-Axis.

  3. Add PercentComplete to X-Axis, right-click, and select Sum.

The example report displays.

:::image type="content" source="media/reports-boards/feature-progress-stacked-bar-chart.png" alt-text="Screenshot of Sample Feature Progress stacked bar chart report.":::

Related articles

[!INCLUDE temp]