Project Reporting Part 5 – Analysing Closure Trends with Azure DevOps Analytics View

What are some of the questions that project team use reporting to help them understand? At the end of a sprint the team might wish to take a look at how work items were closed. In this blog post we take a look at work item closure trends and how Power BI reporting of data from Azure DevOps based on Analytics Views can be used to help with these investigations.

Project Reporting Part 1 – Crucial Contribution Project Team Makes in Reporting
Project Reporting Part 2 – Why Report on Delivery Data with Power BI
Project Reporting Part 3 – Reporting with Power BI for Azure DevOps
Project Reporting Part 4 – Azure DevOps Analytics View Data Model and Power BI Report Designs

In the following sections, we have pulled an “all history” analytics view from the Azure DevOps project called “Smart Track” as discussed in the previous posts. Also new tables are created based on the original Analytics View in order to e.g. aggregate values. This will be done by using common DAX Expressions available in Power BI.

Resolved Reasons for Closed bugs in Azure DevOps

In Azure DevOps, when a bug work item moves to state “Resolved” or “Closed” you can set the “Resolved Reason”. If your team member make use of this “Resolved Reason” field, you can then report on the values used for each bug work item at the end of an iteration.


Assuming all bugs raised in “iteration 1” of this project have had “Resolved Reason” filled in when bugs were closed, we can create a new table using “CALCULATETABLE” DAX Expression to filter for all bugs created in “iteration 1”. Note that “Is Current” field needs to be set to “true” to show all current bugs and their states (see a previous post for explanation of “Is Current” of Azure DevOps Analytics View).


To plot “Resolved Reasons” for all bugs using a Donut Chart, simply insert the field “Resolved Reason” in Details field and the unique “Work Item Id” for values. Additional, insert a filter for “State” of bugs to only include those bugs which are “Closed”.


Number of bugs created for User Stories

For some projects which tracks bugs against user stories, it can be helpful to plot the number of bugs against parent user story work items. The basic assumption is that the project team create bugs and they each bug must be linked to an existing parent User Story. See previous posts for a discussion on the administrative responsibilities of project team members in order to ensure reporting is accurate.


In order to aggregate the number of bugs against each user stories, create a new table called “Bug Count by Parent User Stories” by using the DAX Expressions “SUMMARIZECOLUMNS” and “CALCUATETABLE”. This is possible because when you link a created bug to a parent work item, the field “Parent Work Item Id” is filled in with the ID number of the parent item.


Once the table is created we can plot the aggregated numbers by using e.g. Treemap chart. Simply select “Parent Work Item Id” field for “Group” and “Bug Count” for “Values”. For example, the tree map below shows that user story id 59 has the most number of bugs created (4 in total). This user story will be a good candidate for review at the end of the iteration to see why so many bugs were created during testing or UAT in Azure DevOps.


Plotting Root Problems of Bugs

While out of the box Azure DevOps does not have a field on a bug work item to capture root problem causes of the work item, the project team can easily customise a new field in Azure DevOps to capture this data, or use “tags” on the work item. For example, the following bug is closed and the root problem is identified as “Data Query Issue” (as a tag). If the project team captures this type of information on the closure of a bug work item, we can then plot this data in Power BI.


When using Tags on Work Items in Azure DevOps, this is stored for each Work Item as a list of text separated by semicolons under the “Tag” column in an Analytics View. In Power BI, we can split the values into separate columns by using the “Edit Query” on the Analytics View, then “Split Column” function.


Once this is done, in the new table “Current Bugs” you will see 3 new columns each storing the different tags for the bug work items.


Now we can plot the tag values for each closed bug work items in Power BI. For example, we can use Word Cloud in Power BI to visualise the problem tags based on the “Current Bugs” table above. The more bugs with the same tag values, the bigger the labels will be visualised. For example, if the column “Split Tag1” contains the problem tag values, to aggregate the number of bug work items with the sae tag values, simply set “Count of Work Item Id” in the Values field.


Comparing Original Estimates and Completed Work for Bug Work Items

One final comparison of closure trend that we can do is to compare the original estimates of bug work items against the true effort to complete the bug recorded in “Completed Work” field. If the project team completes these fields in Azure DevOps as part of the administration tasks, we can then plot the 2 values to see how accurate the team’s original estimates were against real effort required to resolve each bug.


To compare the 2 values for each bug work item, we can use a line and clustered column chart. Simply set the “Shared Axis” as the bug unique “Work Item Id”, and “Completed Work” (real effort) and “Original Estimate” fields as the “Column Values” and “Line Values”. As the screenshot below shows, it is worth investigating why bug id 122 has such differing original estimates versus Completed Work. Any learnings from such gap in estimations versus real effort can then be fed back in the retrospective before the next sprint starts.


Closing Trend Power BI Report

While there are many other questions that can be asked specifically for closure reasons of a work item in Azure DevOps project, the examples provided in this post should provide a good beginning to exploring how Power BI visualisations can help with the analysis of closure trends of bug work items.

In putting all of the above 4 charts together in a report page, the Closure Trend Report for my “Fast Track” Azure DevOps project looks like this:



2 thoughts on “Project Reporting Part 5 – Analysing Closure Trends with Azure DevOps Analytics View

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s