Spreadsheets & Data

Calculate a Dashboard Completion Rate Without Averaging Percentages

How do I calculate a task-weighted dashboard completion rate in SQL without averaging project percentages?

Divide summed completed tasks by summed total tasks at the dashboard's reporting level. Retain both counts for later rollups. Use NULLIF on the denominator to keep a team with no tasks undefined, rather than reporting 0%.

SELECT team, SUM(completed_tasks) * 1.0 / NULLIF(SUM(total_tasks), 0) AS completion_rate FROM project_counts GROUP BY team;

What you need

  • DuckDB, MySQL 8.0 or later, or PostgreSQL
  • A scratch database or schema where you can create the sample table
  • One row per non-overlapping project, with consistent completed and total task counts

For a task-weighted dashboard completion rate, divide summed completed tasks by summed total tasks within each reporting group. That gives every included task equal weight. Averaging project percentages gives you a different measure.

Why does a ratio of sums weight completion correctly?

A ratio of sums weights completion by task count: larger projects contribute more tasks to both the completed count and the total count.

completion_rate = SUM(completed_tasks) / SUM(total_tasks)

A 90-task project contributes nine times as many tasks to the denominator as a 10-task project. Those counts already provide the weights. There is no separate weight to store.

This metric answers, “Of all included tasks, what share are complete?” It does not measure effort; that requires a different calculation. A ten-minute task counts as one, just like a two-day task. I would call the card “Task completion” rather than the less precise “Progress.”

An average of project percentages tells you how complete the average project is. That can be useful in some reports, but it gives a small project the same influence as a large one. Pick the measure that answers the report’s question, then decide how to format it.

How do you calculate team completion in SQL?

To calculate team completion in SQL, start with one row per non-overlapping project, sum the completed and total task counts for each team, and divide those sums.

Set the task population and reporting cutoff before writing the SQL. Define which statuses count as complete, and settle whether canceled tasks belong in the total. If canceled tasks are excluded, apply that rule to both counts.

A task must belong to only one included project for this calculation, and each project must belong to one team. Retain project_id even though the rate calculation only needs the team and counts. That identifier lets you check whether project records have been repeated.

The fictional records below provide the sample input:

project_id team completed_tasks total_tasks
MB-101 Team Moon Badger 9 10
MB-102 Team Moon Badger 30 90
WC-201 Team Waffle Comet 18 20
WC-202 Team Waffle Comet 2 5
PD-301 Team Paper Dragon 0 0

Team Paper Dragon has a project record, but that project has no tasks. The calculation needs to preserve this distinction. Having no tasks is not the same as having unfinished work.

Create the sample table in a scratch database or schema. Check first that you will not overwrite an existing project_counts table:

CREATE TABLE project_counts (
    project_id VARCHAR(100),
    team VARCHAR(100),
    completed_tasks BIGINT,
    total_tasks BIGINT
);

INSERT INTO project_counts (
    project_id,
    team,
    completed_tasks,
    total_tasks
)
VALUES
    ('MB-101', 'Team Moon Badger', 9, 10),
    ('MB-102', 'Team Moon Badger', 30, 90),
    ('WC-201', 'Team Waffle Comet', 18, 20),
    ('WC-202', 'Team Waffle Comet', 2, 5),
    ('PD-301', 'Team Paper Dragon', 0, 0);

This query divides the summed completed count by the summed total for each team:

SELECT team, SUM(completed_tasks) * 1.0 / NULLIF(SUM(total_tasks), 0) AS completion_rate FROM project_counts GROUP BY team;

DuckDB’s SUM aggregate adds the completed counts and total counts separately within each team group. The numerator includes * 1.0 to make the fractional calculation explicit.

The setup and the query run unchanged in DuckDB, MySQL and PostgreSQL. MySQL and PostgreSQL return the rate as a decimal with extra trailing zeros, such as 0.3900, which is the same value.

DuckDB’s NULLIF returns NULL when its two arguments match. Here, it turns a zero denominator into NULL. A team with no tasks therefore keeps an undefined completion rate.

The query should return these values:

team completion_rate
Team Moon Badger 0.39
Team Waffle Comet 0.80
Team Paper Dragon NULL

Check the values against their team names. Depending on the display, 0.80 may appear as 0.8; the completion fraction is the same.

Why is Team Moon Badger at 39% instead of 61.67%?

Team Moon Badger is at 39% because 39 of its 100 tasks are complete.

The completed counts form the numerator: 9 + 30 = 39. The total counts form the denominator: 10 + 90 = 100. Dividing gives 39 / 100 = 0.39.

Project MB-101: 9 / 10 = 90%
Project MB-102: 30 / 90 = approximately 33.33%
Unweighted average: approximately 61.67%
Correct task completion rate: 39%

The smaller project is nearly finished, but it holds only one-tenth of the team’s tasks. Averaging the two project percentages gives that project half the influence. The resulting percentage overstates the share of tasks actually completed.

Team Waffle Comet shows the opposite error. Its project rates are 18 / 20 = 90% and 2 / 5 = 40%, which average to 65%. Using the task counts instead gives (18 + 2) / (20 + 5) = 20 / 25 = 80%.

Averaging project percentages can push task completion either too high or too low. It depends on the relationship between project size and completion. A fixed adjustment applied after averaging cannot repair that mismatch.

How should you display rates and calculate an overall total?

Show defined rates as percentages, leave undefined rates undefined, and use the underlying task counts to calculate overall completion.

The fraction 0.39 should display as 39%. Check whether the presentation layer expects fractions or percentage points before converting it. Convert only once: multiplying by 100 twice turns 39% into 3900%.

Show “No tasks” or “N/A” for Team Paper Dragon, but retain NULL in the SQL result. Showing 0% would say the team has tasks and has finished none of them. If missing projects need investigation, consult the guide to finding projects with no tasks before choosing the completion card’s display.

Across the full sample, the counts add up to 39 + 20 + 0 = 59 completed tasks and 100 + 25 + 0 = 125 total tasks. Overall completion is therefore 59 / 125 = 47.2%. Averaging the two defined team rates would produce 59.5% instead.

Team percentages can only produce the overall rate if their corresponding task totals are also available. The percentages alone are not enough. I prefer keeping both counts beside the rate so regrouping the data does not depend on rounded percentages.

Both counts need the same population filters. Dividing tasks completed this week by all-time tasks uses a different scope than measuring completion within a fixed task population. The spreadsheets and data topic page covers other reporting tasks where the scope needs to be explicit.

Which data problems can make the rate wrong?

Duplicate projects, missing counts, and invalid counts can all produce a misleading completion rate, even if the displayed percentage looks reasonable.

Repeated project rows

Joining projects to assignees or labels can repeat a project’s counts once for each matching row. Before aggregating, restore one authoritative pair of counts per project.

Use this query to locate repeated project identifiers:

SELECT project_id, COUNT(*) AS row_count
FROM project_counts
GROUP BY project_id
HAVING COUNT(*) > 1;

Remove duplicate project records without changing their numeric counts. Identical completed-task counts are not proof of duplication: two different projects can legitimately have the same count.

Missing counts

According to DuckDB’s documentation, general aggregate functions ignore NULL inputs, with exceptions. A missing completed count or total count can therefore leave the numerator and denominator summing different populations.

Check for records missing either count:

SELECT project_id, team, completed_tasks, total_tasks
FROM project_counts
WHERE completed_tasks IS NULL
   OR total_tasks IS NULL;

Get missing counts from the source before publishing the rate. Substitute zero only when the source definition explicitly treats missing as zero. An unknown completed count does not tell you that no tasks are complete.

Invalid counts

Reject negative counts and any completed count greater than its total. Bad values can cancel each other out, producing a team percentage that looks perfectly believable.

Run this check before aggregating the rows:

SELECT project_id, team, completed_tasks, total_tasks
FROM project_counts
WHERE completed_tasks < 0
   OR total_tasks < 0
   OR completed_tasks > total_tasks;

Fix the source counts rather than limiting the displayed rate to 100%. I would not use a plausible dashboard percentage to conceal an impossible task count.

When should you change the project-count method?

Use a different approach when tasks belong to multiple projects, snapshots repeat the same tasks, or the report needs effort completion rather than task completion.

Condition Required change
Tasks appear in multiple projects Start from task-level records and establish one included record per task at the reporting level.
Daily snapshots repeat project counts Select the latest valid snapshot per project for current state; group by snapshot date and team for history.
The question concerns effort Divide completed effort by total planned effort using consistent units and a distinct label.
The question concerns the average project An unweighted average may fit; label it “Average project completion” and define treatment of zero-task projects.

For current-state reporting, choose the latest valid snapshot per project before summing. Adding yesterday’s snapshot to today’s counts the same task population more than once. Historical reporting needs a separate rate for each snapshot date and team.

When the source is task status history rather than project snapshots, keep the latest status row per task before calculating current project counts. Removing duplicate project totals cannot correct tasks that were counted twice earlier in the pipeline.

Effort completion requires the same effort unit and inclusion rules in both counts, plus its own label. Replacing task counts with estimates while leaving the label as “Task completion” misstates what the dashboard measures.

Sources

Esc