How revenue doubles while the dashboard says everything is fine

Execution success, lineage failure

A refresh macro can log start time, end time, rows loaded, connection result, and still return success while revenue doubles. It records that the query ran. It does not record whether rows from the source arrived once, whether the join key was unique on the one side, or whether the final row count matched the grain of the fact table.

Most dashboard automation treats an error as a hard failure: a syntax error, a type mismatch, a connection timeout, a missing column. A fanout join is not a hard failure. It is a valid relational result built on an invalid assumption. The result set is larger, the measure is inflated, and no step in the pipeline raises an exception.

This is the gap between execution and lineage. Refresh success validates execution. Record-count reconciliation validates lineage. Teams that trust refresh status are only validating that the machine followed instructions, not that the instructions preserved the underlying information.

Where the doubling starts

The most common failure pattern is a join where the one side is not actually unique. A revenue query typically joins a fact table to a dimension table. The expectation is that each fact row matches one dimension row. When the dimension contains duplicate keys, each matching fact row repeats.

SQL COUNT semantics make the problem easy to see when the check runs. COUNT(*) counts each row separately, including duplicate rows. COUNT(DISTINCT) counts only unique non-null values. When a join key is duplicated on the one side, the difference between these two counts is the size of the fanout.

A finance team can run a simple check: COUNT(*) on the joined result should equal the row count of the fact table at the analysis grain. If the fact table has 4,971 invoice line rows before the join and 9,942 rows after, the revenue measure has doubled because every line matched twice.

Why visuals and totals miss the fanout

Power BI can enforce uniqueness on the one side of a relationship. If a refresh tries to load duplicate values into the one side, the refresh fails. But many finance teams do not rely on that enforcement because they join in SQL before Power BI. By the time the flat table arrives, the model has no relationship to validate.

Once the duplicated rows reach the model, every visual renders. A measure is only an aggregate over whatever rows the query returned. A total, a variance percentage, or a trend line does not carry any record of the expected grain. It cannot signal that 4,971 source rows became 9,942 result rows.

The total can also look plausible. If a prior period was built from the same duplicated account mapping, the month-over-month variance may stay within the normal 2% band. The dashboard appears stable, the commentary writes itself, and nobody sees a reason to open the query editor.

Four record-count checks before the KPI

One: count the source rows at the grain of the fact table before any join. Log that number in a refresh control table.

Two: count the rows in the final query and compare it to the expected grain. If the join is supposed to be one-to-one or one-to-many with a unique dimension, the final count must equal the fact table count.

Three: check uniqueness on every join key. On the dimension table, compare COUNT(*) with COUNT(DISTINCT join_key). If they differ, the join will fan out the fact table.

Four: add a control total to the batch. A row count catches duplication; a hash or checksum over key business columns catches value drift. The batch should fail closed when the row count or hash does not match the expected control.

A worked example: the duplicated account mapping

A finance team refreshes a daily revenue dashboard from a table called fact_revenue_line. The table has 4,971 rows, one row per invoice line, with a gross_amount column. The query joins fact_revenue_line to dim_customer_account on account_id.

dim_customer_account should be unique by account_id, but a CRM-to-data-warehouse sync has run twice. The table now has 2,492 rows instead of 1,246, and every account appears twice.

The join returns 9,942 rows because every fact row matches two dimension rows. SUM(gross_amount) doubles. The refresh macro writes a log entry: Success — 9,942 rows loaded. The team has no expected row count at the invoice-line grain, so no one compares 9,942 to 4,971.

The month-over-month revenue variance stays within 2% because the prior month used the same duplicated account mapping. The dashboard says everything is fine. The revenue total is not fine.

The detection is a single SQL statement: SELECT COUNT(*) AS total_rows, COUNT(DISTINCT invoice_line_id) AS distinct_lines FROM joined_query. The first count returns 9,942; the second returns 4,971. That difference is the entire revenue overstatement.

Limitation: Where the record-count check stops

Limitation: row-count equality is necessary but not sufficient. If two different invoice lines share the same invoice_line_id because of a source-system reset, COUNT(*) and COUNT(DISTINCT invoice_line_id) can both match the expected row count while revenue is allocated to the wrong invoice. A hash over the full row, or over business keys plus amounts, catches value drift, but still requires a person to define the expected grain and decide whether a flagged duplicate is legitimate.

Row-count controls identify multiplication. They do not validate that the right rows remained matched, that exchange rates were applied at the correct date, or that the duplicated account is the one that should be excluded. Automation can log a mismatch; it cannot make the judgment call.

Reconcile before presentation

A dashboard should not be trusted until the row counts and join keys have been reconciled. The refresh log is a useful execution log, but it is not a data-integrity log. The two must run side by side. When a total reaches a board pack, the team should be able to point to a control record that shows the source row count, the expected grain, the final row count, and a pass or fail status for that batch.