遵循SAFe框架，如何在ADO中获取多迭代/PI下团队总容量与总工作量？

针对SAFe框架下多团队跨迭代/PI的容量与工作量汇总需求，以下是三种可行实现方案：

方案一：ADO内置工具组合（轻量无额外依赖） #

1. 汇总各团队迭代容量 #

ADO无直接跨团队的容量聚合功能，需批量收集后汇总：

• 切换到目标团队的看板视图，进入Sprint > Backlog标签页
• 选择对应迭代，记录页面顶部的团队容量数值
• 重复操作收集所有目标团队在目标迭代/PI内的容量数据，用Excel或手动完成团队维度及总容量的汇总

2. 统计计划总工作量（原始估算总和） #

通过ADO查询功能批量筛选并聚合：

• 进入项目Boards > Queries页面，新建Flat query
• 设置筛选条件：
  • Work Item Type = Task（或包含估算的工作项类型）
  • Iteration Path 选择所有目标迭代（支持多选或选择PI对应的迭代路径组）
  • Team 选择所有目标团队
• 添加列：Original Estimate、Team、Iteration Path
• 点击页面顶部Export to Excel，在Excel中按团队、迭代分组求和，得到各团队及整体的计划总工作量

3. 仪表板可视化（可选） #

将上述查询添加到项目仪表板，使用Chart小组件选择Sum聚合Original Estimate，按Team或Iteration Path分组展示；容量数据可手动更新到仪表板的Markdown小组件，实现可视化汇总。

方案二：Power BI集成（适合多维度复杂统计） #

1. 连接ADO数据源 #

打开Power BI Desktop，选择Get Data > Azure > Azure DevOps Services（或Azure DevOps Server），输入组织URL和项目名称，导入工作项数据及团队迭代信息。

2. 构建数据模型 #

• 在Power Query编辑器中，导入从ADO导出的各团队迭代容量Excel数据（或通过API批量获取）
• 将工作项数据与容量数据按Team和Iteration Path关联，构建完整数据模型

3. 创建可视化报表 #

• 用卡片组件展示总容量、总计划工作量
• 用柱状图或矩阵按团队、迭代分组，展示各维度的容量与工作量对比
• 发布报表到Power BI服务，嵌入到ADO仪表板实现实时同步

方案三：ADO REST API脚本（自动化批量统计） #

通过调用ADO REST API实现自动化数据收集与汇总，以PowerShell脚本为例：

1. 获取目标迭代列表 #

$orgUrl = "https://dev.azure.com/your-org"
$project = "your-project"
$piPath = "\Your PI Path"
$patToken = "your-pat-token"

$iterations = Invoke-RestMethod -Uri "$orgUrl/$project/_apis/work/teamsettings/iterations?depth=2&api-version=7.1-preview.1" -Method Get -Headers @{Authorization = "Bearer $patToken"}
$targetIterations = $iterations.value | Where-Object { $_.path -like "$piPath*" }

2. 获取各团队迭代容量 #

$teams = @("Team1", "Team2")
$capacityData = @()

foreach ($team in $teams) {
    foreach ($iteration in $targetIterations) {
        $capacity = Invoke-RestMethod -Uri "$orgUrl/$project/$team/_apis/work/teamsettings/iterations/$($iteration.id)/capacity?api-version=7.1-preview.3" -Method Get -Headers @{Authorization = "Bearer $patToken"}
        $totalCapacity = ($capacity.teamMembers | Measure-Object -Property capacitySum -Sum).Sum
        $capacityData += [PSCustomObject]@{
            Team = $team
            Iteration = $iteration.name
            TotalCapacity = $totalCapacity
        }
    }
}

3. 获取计划工作量并汇总 #

# 构建WIQL查询语句
$queryIterations = $targetIterations.path -join "','"
$queryTeams = $teams -join "','"
$wiqlQuery = "Select [System.Id], [System.Team], [System.IterationPath], [Microsoft.VSTS.Scheduling.OriginalEstimate] From WorkItems Where [System.WorkItemType] = 'Task' And [System.IterationPath] In ('$queryIterations') And [System.Team] In ('$queryTeams')"

# 执行查询获取工作项ID
$queryResult = Invoke-RestMethod -Uri "$orgUrl/$project/_apis/wit/wiql?api-version=7.1-preview.2" -Method Post -Headers @{Authorization = "Bearer $patToken"; "Content-Type" = "application/json"} -Body (@{query = $wiqlQuery} | ConvertTo-Json)
$workItemIds = $queryResult.workItems.id -join ","

# 获取工作项详情并汇总工作量
$workItems = Invoke-RestMethod -Uri "$orgUrl/$project/_apis/wit/workitems?ids=$workItemIds&fields=System.Team,System.IterationPath,Microsoft.VSTS.Scheduling.OriginalEstimate&api-version=7.1-preview.3" -Method Get -Headers @{Authorization = "Bearer $patToken"}
$workloadData = $workItems.value | Group-Object -Property @{Name='Team'; Expression={$_.fields.'System.Team'}}, @{Name='Iteration'; Expression={$_.fields.'System.IterationPath'}} | ForEach-Object {
    [PSCustomObject]@{
        Team = $_.Group[0].fields.'System.Team'
        Iteration = $_.Group[0].fields.'System.IterationPath'
        TotalWorkload = ($_.Group.fields.'Microsoft.VSTS.Scheduling.OriginalEstimate' | Measure-Object -Sum).Sum
    }
}

4. 合并输出汇总结果 #

# 合并容量与工作量数据
$mergedData = Merge-Object -Left $capacityData -Right $workloadData -On Team, Iteration

# 按团队汇总
$teamSummary = $mergedData | Group-Object Team | ForEach-Object {
    [PSCustomObject]@{
        Team = $_.Name
        TotalCapacity = ($_.Group.TotalCapacity | Measure-Object -Sum).Sum
        TotalWorkload = ($_.Group.TotalWorkload | Measure-Object -Sum).Sum
    }
}

# 整体汇总
$overallSummary = [PSCustomObject]@{
    TotalCapacity = ($teamSummary.TotalCapacity | Measure-Object -Sum).Sum
    TotalWorkload = ($teamSummary.TotalWorkload | Measure-Object -Sum).Sum
}

# 输出结果
Write-Output "团队维度汇总："
$teamSummary | Format-Table
Write-Output "`n整体汇总："
$overallSummary | Format-Table

内容的提问来源于stack exchange，提问作者Krishna Punekar