Easily Create a Work Item Rollup Calculations Report

Create Work Item Rollup Calculations with a Hierarchy Data Model in Power BI

Generating reports on Azure DevOps (ADO) work items in Power BI can be quite challenging, especially when dealing with hierarchical relationships. ADO’s current reporting capabilities only support a single-level relationship, linking child work items to their parent through the ParentWorkitemID field. This limitation complicates the process of creating reports that accurately reflect the hierarchical structure of work items, making it difficult to generate calculations for lower-level work item types, such as tasks, which roll up to higher-level work item types like Epics or Features. Consequently, teams struggle to gain insights into their project’s progress at the feature or epic level due to the lack of work item rollup calculations.

In this blog post, we will explore how to implement a parent/child data model for work item rollup calculations and easily implement summary calculations from children workitems to the parents.

The ADO Workitem Hierarchy

Azure DevOps (ADO) organizes work items into a structured hierarchy. At the Portfolio level, it includes Initiatives, Themes, Epics, and Features. The Backlog level comprises User Stories, Tasks, and occasionally Bugs. The specific work item types available depend on the process template used to create the team project.

There is a linear path from a user story to an epic. However, a task can be associated with a Bug or User Story. An issue can be associated with any workitem type. This causes issues when attempting to link the child to the upper-level parent workitem types.

This blog post shows the workitem types from the Agile process template and has the following hierarchical structure:

ODATA Query for Child Workitems

To provide multi-level relationships in reporting, an ODATA query is needed with the descendants clause. This query retrieves all related child work items across various levels and links them back to the parent work item when creating measures. Review this blog post on getting started with writing and testing ODATA queries.

By utilizing the Descendants/any() clause and the $expand Descendants clause, the query efficiently gathers the necessary data. The advantage of this approach is its flexibility; the ODATA query can be easily modified to include additional columns for calculations and adjusted to meet specific reporting needs.

However, it does return every child descendant of the work item, necessitating filtering either in the table or through measures to ensure accuracy. The resulting dataset from the ODATA query includes the work item, its type, the parent work item ID, and the associated descendant work items.

Implement the Workitem Hierarchy

This implementation requires several ODATA queries to create the workitem fact table and the secondary child workitem table.

Workitem Fact Table

Create an ODATA query to return the workitems for the fact table, or the main source of data for the report. This blog discusses setting up a fact table with ODATA or Analytic Views. This should include the workitem types that appear on the report’s visuals. Add the fields to the $select part of the ODATA query that are needed for the visuals.

https://analytics.dev.azure.com/<ADO-Org>/<Team-Project>/_odata/v4.0-preview/WorkItems?
$select=WorkItemId,WorkItemType,State,ParentWorkItemID
$filter=(CreatedDate ge 2020-01-01Z) and (State ne 'Removed') and (WorkItemType eq 'Epic' or WorkItemType eq 'Theme' or WorkItemType eq 'Initiative' or WorkItemType eq 'Feature' or WorkItemType eq 'User Story')

NOTE: An Analytics View can be used in place of the ODATA query. Follow the steps in this blog for creating a star schema with an Analytics View.

The child workitem table can only be achieved using an ODATA query. Check out this blog for working with ODATA queries.

Child Workitem Table

The ODATA query below retrieves the primary work item types (Initiative, Theme, and Epic) along with their subordinate work item types (Theme, Epic, Feature, User Story, and Issue). Themes and Epics are included in the descendant query because they are children of the Initiative work item, which sits at the top of the hierarchy. For more information on the Descendants ODATA clause, check out this help file.

https://analytics.dev.azure.com/<ADO-Org>/<Team-Project>/_odata/v4.0-preview/WorkItems?
$select=WorkItemId,WorkItemType,State,ParentWorkItemID
$filter=(CreatedDate ge 2020-01-01Z) and (State ne 'Removed') and (WorkItemType eq 'Epic' or WorkItemType eq 'Theme' or WorkItemType eq 'Initiative') and Descendants/any()
&$expand=Descendants($levels=max;$select=WorkItemType,WorkItemId,ParentWorkItemID,State,Effort,StoryPoints,Priority;$filter=((CreatedDate ge 2020-01-01Z) and (State ne 'Removed') and (ParentWorkItemID ne null) ) and (WorkItemType eq 'Epic' or WorkItemType eq 'Feature' or WorkItemType eq 'Theme' or WorkItemType eq 'User Story' or WorkItemType eq 'Issue'))

ODATA Query Breakout:

The first line in the ODATA query is the URL for the ADO organization, Team Project, and the ADO entity, which is Workitems. Change the <Your-ADO-Org> and <Your-Team-Project> to the respective values. Check out this blog post on how to find your ADO organization and team project names.

https://analytics.dev.azure.com/<Your-ADO-Org>/<Your-Team-Project>/_odata/v4.0-preview/WorkItems?

The $select clause returns the fields for the Parent workitems. Add additional fields as needed for your report.

$select=WorkItemId,WorkItemType,State,ParentWorkItemID

The $filter clause is returning only the workitems created after 1/1/2020 and do not have the State Removed and return the top level workitem types, Epic, Theme, or Initiative.

To ensure your OData query returns all descendants of the top-level work items, it is crucial to include the Descendants/any() clause. This clause filters the work items to include only those with descendants, which is essential for expanding and retrieving the hierarchical data structure.

$filter=(CreatedDate ge 2020-01-01Z) and (State ne 'Removed') and (WorkItemType eq 'Epic' or WorkItemType eq 'Theme' or WorkItemType eq 'Initiative') and Descendants/any()

The $expand clause expands the descendant record’s fields and the $levels=max will return all levels in the hierarchy. The $select clause returns the fields for the child workitems. These fields are used for summary or roll up calculations for the top level workitems.

&$expand=Descendants($levels=max;$select=WorkItemType,WorkItemId,ParentWorkItemID,State,Effort,StoryPoints,Priority;$filter=((CreatedDate ge 2020-01-01Z) and (State ne 'Removed') and (ParentWorkItemID ne null) ) and (WorkItemType eq 'Epic' or WorkItemType eq 'Feature' or WorkItemType eq 'Theme' or WorkItemType eq 'User Story' or WorkItemType eq 'Issue'))

Make adjustments as needed to return the child workitems for your report.

Follow the steps in this blog to add the ODATA to a Power BI report and begin the report design process.

NOTE:

When modifying the ODATA query with new fields that are part of the Descendants portion of the query, you must go back to the Expand step and select the new fields. Here I added the Title, StartDate, and TargetDate fields. I had to select them in the Expand step and they appeared in the results pane.

Power BI Relationships

Create a one to many relationship between the Workitems fact table and the Childworkitems table on the WorkitemID field. The ChildWorkitems table contains child workitems (workitems related by the ParentworkitemID field), and descendant workitems which have multi-level relationships between workitems.

The relationship between the Workitems fact table and the Descendants, or child workitem table, enables the display of related workitems to their parent. In this example, the top-level workitems—Initiative, Theme, and Epic—are shown in the top table and filtered by the slicer. When selected, the related child workitems are displayed in the lower table. These can be further filtered by another slicer for more control over the display. These tables function as separate entities for filtering.

DAX Measures – Work Item Rollup Calculations

The data model and relationship in Power BI make creating DAX measures extremely easy between the workitem fact table and the child workitem table to create the work item rollup calculations.

This measure works at the Initiative workitem type level. It sums the total Effort from the linked Feature workitems.

Effort-Total =
// Sets the current workitem ID to the variable to use to search the child table 
var _workitemID = SELECTEDVALUE('Worktitems'[WorkItemId])

// Sum the Effort field for the child workitems that match the parent workitem ID
var _issueCount = CALCULATE(
        SUM(ChildrenWorkitmes[Descendants.Effort]),
        'ChildrenWorkitmes'[WorkItemId] = _workitemID,
        'ChildrenWorkitmes'[Descendants.WorkItemType] = "Feature"    
    )
var _return = _issueCount
return _return

COUNT ALL CHILDREN

This measure counts the total children workitems to the parent. The measure retrieves the current Parent Workitem ID and sets to the variable _workitemID. This variable is used to filter the Count Rows function.

Child-All-WI-Count = 
//Counts the number of children workitems for the selected higher level WI type. Can be used at the top level workitems
var _workitemID = SELECTEDVALUE('Workitems'[WorkItemId])
var _wiTotal = CALCULATE(
       COUNTROWS('ChildWorkitems'),
        'ChildWorkitems'[WorkItemId] = _workitemID
    )
var _result = IF(ISBLANK(_wiTotal),0,_wiTotal)
return _result

FEATURES COMPLETED and the EFFORT

This is an example of a summary or rollup measure. It sums the total value in the Effort column for the Feature child workitems under the parent. This can be used at the Product Backlog level workitems, such as Initiative, Theme, Epic, or Feature and summarize the User Stories, or Tasks that are children to the parents. It will work at any level in the hierarchy if the filter is changed.

Effort-Completed = 
//Sums the total Effort for Features only for Closed workitems. Can be used at the top level workitems
var _workitemID = SELECTEDVALUE('Workitems'[WorkItemId])
var _effortTotal = CALCULATE(
        SUM(ChildWorkitems[Descendants.Effort]),
        'ChildWorkitems'[WorkItemId] = _workitemID,
        'ChildWorkitems'[Descendants.WorkItemType] = "Feature" && ChildWorkitems[Descendants.State] in {"Closed","Resolved"}   
    )
var _result = IF(ISBLANK(_effortTotal),0,_effortTotal)
return _result

The DAX measures start the calculation from the Workitem, or fact table, and use the parent/child relationship to summarize the data in the child workitem table. The pattern for these measures is the same. Obtain the workitem ID on the parent and use it to filter the data on the child workitem table.

This is a very easy data model to use for rollup and summary calculcations. It scales and allows for quick calculations on thousands of workitems.

Let me know if you encounter any issues.