Is there a way to total estimates and completed work in Azure DevOps queries?

Question

We've come across a number of issues with our Azure DevOps projects and are trying to surface relevant information to the management team with queries and dashboards on projects. Mainly it's just been counting the number of results for particular queries, e.g. when a status hasn't changed in 30 days, number of blocked items, total items in current sprint etc.

What we've been asked for though is to be able to rollup the original estimate total for all work items, and also roll up the completed work as another value. The queries and other things I've seen only seem to be able to count, rather than sum up, but some of the widgets I've seen do appear to sum things for graphs (but I'm just looking for the values).

Can anyone suggest anything?

Answer 1

I imagine that you have two different options here. The first being is that you could leverage the new roll-up columns and aggregate some of this information on the backlog view. Some of this makes assumptions about how you are grouping and the hierarchy of your work items.

Add a rollup column

1. In the Column options dialog, choose Add a rollup column, select From quick list, and then choose from one of the options listed.
2. Choose from the menu provided.

• Progress bar displays progress bars based on the percentage of associated descendant work items which have been completed or closed.
• Total number displays the sum of descendant items or the associated fields of descendant items. Totals provide a measure of the size of a Feature or Epic based on the number of its child items. For example, Count of Tasks shows the sum of all tasks that are linked to parent items. The active or closed state is ignored. Rollup column menu

3. Remaining Work of Tasks shows the sum of Remaining Work of tasks that are linked to the parent item.

If you wanted to instead see the summarized details on a dashboard, I'd recommend downloading the Query Tile PRO marketplace extension. Let's say you had a query already defined:

The options support sums based on query fields:

And so you have the tile with the summation value you are wanting. Just replace with other fields that you might need.