Operations & Systems

Two Dashboards, Two Totals: Reconcile the Definition Before the Number

One dashboard shows nine completed requests. Another shows six. Someone proposes refreshing both until they agree.

Preserve the disagreement first.

Write down what each total claims to measure. Retain the underlying records. Then explain the difference with identifiable rows and arithmetic. Agreement becomes useful when you can explain what changed.

The worksheet below investigates operational service-ticket counts. Every record and decision in the worked example is fictional. The method is an editorial proposal, not a product requirement or a report of client work.

Freeze the Comparison Before Investigating

Keep both displayed values, report versions, filter selections, and extracts. Record when you observed them. Avoid changing the live reports while collecting the comparison evidence.

A screenshot preserves the displayed number. It does not establish every rule behind that number. Retain the query or calculation reference and the permitted record-level export when available. Use synthetic records for training and public examples.

Copy this comparison sheet for the disputed metric:

Question Dashboard A Dashboard B
What business question does it answer? [Exact question] [Exact question]
What is the counting unit and input grain? [Request, event, or joined row] [Request, event, or joined row]
Which population and exclusions apply? [Eligibility rules] [Eligibility rules]
Which statuses qualify? [Named states] [Named states]
Which timestamp selects the period? [Field and boundary rule] [Field and boundary rule]
Which time zone applies? [Zone] [Zone]
Which visible and hidden filters apply? [Selections and model filters] [Selections and model filters]
When did the source last refresh? [Successful time and watermark] [Successful time and watermark]
When was this comparison captured? [Observation timestamp] [Observation timestamp]
Who owns the definition? [Approver and version] [Approver and version]

Mark missing answers unknown. A shared report title does not prove a shared population. “Completed this month” can describe requests completed during the month or requests due during the month that later completed.

The operator dashboard guide addresses indicator selection. This investigation starts after selection, when two displayed totals need an explanation.

Put Both Reports on an Explicit Time Basis

Separate the business interval from the time the source became available. A completion timestamp can fall inside yesterday while its reporting record arrives today.

For the fictional example, both reports intend to count completions from 2026.10.26 00:00 UTC to before 2026.10.27 00:00 UTC. Both exclude test tickets and cancellations. Both cover the same service queue and have no additional selected filters.

Dashboard A uses an extract produced at 2026.10.27 08:00 UTC. Dashboard B uses an extract produced at 10:00 UTC. The comparison is captured at 10:15 UTC. Keep all three times in the record.

If real reports use different time zones, translate their boundaries before comparing records. A shared calendar label can hide different timestamp intervals. If the time zone is unknown, leave that part of the bridge unresolved.

Choose a comparison policy before rerunning anything. You can compare the retained snapshots as observed, then explain arrival differences. You can also create a separate comparison using a common source watermark. Label the new comparison separately.

The example follows the retained-snapshot approach. It does not claim that either snapshot contains every event from the business interval.

Inspect the First Fictional Ticket Table

Dashboard A counts rows from a flattened ticket-and-tag extract. Each row represents one ticket-tag pair. Tickets can have multiple tags. Its completion rule includes both Closed and Resolved.

Every completion timestamp below falls inside the stated UTC interval. The extract contains no test tickets or cancellations.

Ticket Status Tag Completion time on October 26, UTC
T101 Closed Intake 08:00
T101 Closed Priority 08:00
T102 Closed Intake 09:00
T102 Closed Priority 09:00
T103 Closed Intake 10:00
T104 Closed Intake 11:00
T105 Resolved Intake 12:00
T106 Closed Intake 13:00
T107 Closed Intake 14:00

Counting rows produces nine. Counting distinct ticket identifiers produces seven. The two extra contributions come from T101 and T102, each appearing twice after tag expansion.

This is an example of a flattened join changing row grain. It is not a claim that creating a Power BI relationship automatically duplicates a table. Microsoft's star-schema guidance recommends consistent fact-table grain. Here, the extract's ticket-tag grain conflicts with a request-count label.

Preserve the tags if they serve a legitimate reporting purpose. Change the calculation or prepare a ticket-grain input for a ticket count. Deleting whichever rows look repetitive can destroy the tag information without resolving the metric definition.

Inspect the Second Fictional Ticket Table

Dashboard B counts one row per ticket. It includes Closed only. Its later extract contains T108, whose completion event arrived in the reporting dataset at 09:00 UTC on October 27.

T108 completed during the business interval. Its arrival happened after A's extraction and before B's extraction. Those are supplied facts of this fictional example.

Ticket Status Completion time on October 26, UTC Evidence note
T101 Closed 08:00 Present in both extracts
T102 Closed 09:00 Present in both extracts
T103 Closed 10:00 Present in both extracts
T104 Closed 11:00 Present in both extracts
T106 Closed 13:00 Present in both extracts
T108 Closed 15:00 Arrived in reporting data October 27 at 09:00 UTC

The displayed total is six. T105 is absent because B excludes Resolved. T108 supplies a known timing difference. T107 is also absent, but the retained evidence does not explain its absence.

Do not label T107 deleted, misassigned, or filtered without evidence. Put its identifier in the unresolved list. Assign someone to inspect the permitted source history and calculation inputs.

For a Power BI investigation, inspect relationship cardinality and filter paths separately from exported rows. Microsoft's relationship documentation explains that relationships propagate filters and do not enforce data integrity. A matching diagram alone cannot settle the record-level discrepancy.

Build a Bridge With a Separate Residual

Choose a proposed comparison definition: distinct tickets with status Closed and a completion timestamp inside the stated interval. Use the later extract's known arrival evidence for this bridge.

Begin with A's displayed nine. Apply each explained adjustment once. Identify the records behind it.

Bridge step Adjustment Running total Supporting records
Dashboard A as observed None 9 Nine ticket-tag rows
Remove extra tag contributions -2 7 Second contributions for T101 and T102
Apply Closed-only rule -1 6 T105 is Resolved
Include documented later arrival +1 7 T108
Expected total after explained adjustments None 7 Seven candidate Closed tickets
Dashboard B as observed None 6 Six ticket rows
Unresolved residual: observed B minus expected -1 Not an explained adjustment T107 lacks an established exclusion reason

The explained arithmetic is nine minus two minus one plus one equals seven. B shows six. Six minus seven leaves a residual of negative one.

Do not insert an “other” adjustment to force agreement. The residual is a finding. It remains separate until evidence identifies its cause or the owner accepts a documented limit.

Keep adjustment sets disjoint. T105 appears once in A, so the status change removes one contribution. T101 and T102 account for the two duplicate contributions. T108 accounts for the later arrival. No record supplies two adjustments here.

For larger comparisons, retain the identifiers behind each category in a controlled worksheet. A total can balance while records still disagree. Compare membership as well as counts.

Separate Different Meanings From Incorrect Data

The example contains three different kinds of difference.

The tag expansion is a counting mismatch if A claims to count tickets. Counting ticket-tag pairs can be legitimate, but it needs a label that describes that unit.

The status rule is a definition difference. An operations owner might accept Resolved as completion, while another decision requires Closed. Neither choice becomes correct merely because it produces a familiar total. Record which question each rule answers.

The arrival difference concerns source availability. It does not prove that the ticket completed late. Keep the completion timestamp and reporting-arrival timestamp separate.

T107 remains unexplained. The available evidence supports an investigation, not a cause. This distinction matters when totals inform decisions about work. A report discrepancy alone does not establish employee effort or misconduct.

The dbt metrics documentation, for dbt v1.12 and later, lists metric types and optional filters in explicit YAML definitions. Use that as an example of visible configuration. The worksheet does not require dbt, and no dbt implementation was tested here.

Keep the eventual business definition readable even when its implementation lives in configuration. Someone must approve the meaning before a builder aligns the two reports.

Record the Owner's Decision Before Restating History

End the investigation with a decision record, including unresolved evidence. A fictional owner chooses this outcome:

“Use distinct Closed tickets for the completion count. Retain ticket-tag counts under a separate label. Treat the later extract as the comparison snapshot. Hold final acceptance until T107 is explained.”

Record the approver, decision date, definition version, affected reports, and implementation owner. Assign a review point for T107. Mark acceptance pending rather than converting the provisional seven into an approved historical total.

Preserve the original nine and six as observed values. If the owner later authorizes a replacement, retain both the earlier value and the reason for its revision.

A proposed restatement label reads: “Restated under completion definition v2 using the October 27 10:00 UTC extract; distinct Closed tickets; supersedes the earlier display.” Keep that label proposed until the residual is resolved and the owner accepts the result.

If historical source evidence is missing, record the affected periods as unreconciled. A current query cannot reconstruct an unavailable past state by declaration. Keep a break in the comparison where necessary.

Return the Finding to the Reporting Workflow

Use reporting automation to carry the approved definition version, period, source watermark, and result state into future reports. The automation should preserve an unavailable result when it cannot establish the required evidence.

When applying the worksheet to CRM dashboards, begin with one metric and its exact counting unit. Do not expand a ticket-count investigation into forecast methods or attribution comparisons. Each metric needs its own record boundary.

Before closing the investigation, have a reader reproduce the bridge from the retained tables. Confirm that each adjustment has evidence and an owner. Keep the unresolved list visible until its decision is recorded.

The work ends with an explained number and a recorded decision.

Sources

Topic map: Spreadsheets, CSV files, and data handoffs

Articles in this cluster, then the 3 clusters that share the most topics with it.

This cluster: Spreadsheets, CSV files, and data handoffs

Neighbor: Business reporting and statistics

Neighbor: Records, access, and continuity

Neighbor: APIs, webhooks, and automation