Skip down to the research record

Pipeline report errors: 4 mistakes to trace in totals

Quota Forecast Editorial Team · Published · 10 min read

A pipeline report total can mislead for four documented reasons: a zero from missing amounts, unsafe division, a skipped date, and a late refresh.

Key takeaways

  • Missing input can appear as a zero total because an empty or all-NA Series sums to 0 by default, according to pandas.
  • Do not assume CASE protects every division, as there are various situations in which subexpressions of an expression are evaluated at different times, so that the principle that “CASE evaluates only necessary subexpressions” is not ironclad, according to PostgreSQL.
  • Distinguish the previous row from the previous calendar day, since the value in the Previous column for October 4th is 3, which is the value of the previous row, according to Metabase.
  • Check refresh status instead of treating the scheduled slot as completion, noting the target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can't allocate the required resources sooner, according to Microsoft Learn.

Missing amounts become zero totals

A pipeline total that reads as zero may reflect missing input rather than a true absence of revenue. When a data series is empty or contains only missing values, the sum calculation can return zero by default. The pandas.DataFrame.sum documentation states that it excludes NA/null values when computing the result according to pandas. Consequently, if every amount field in a specific segment is null, the aggregation process skips those entries and produces a zero figure. This zero appears in the report as a valid numeric value, masking the underlying data gap. Revenue leaders reviewing coverage must distinguish between a genuine zero-pipeline state and an artifact of missing inputs. See Forecast total mismatches: 4 calculation mistakes to check when one coverage total looks off. The visual similarity between these two states creates risk during forecast discussions. A team might interpret a zero total as a complete loss of pipeline, so check the aggregation logic before you assign that cause. Identifying this distinction requires checking the raw source records for null values in the amount fields. In this case, no values are available for summation. The result is a zero that looks like a definitive metric but represents an absence of information. Reviewers should verify whether the deals exist in the system with stage and date fields populated but amount fields left blank. Without a count of non-null values alongside the sum, the reader cannot determine if the zero comes from one missing record or thousands. The documentation notes that this empty sum behavior can be controlled with the min_count parameter according to pandas. The goal is to prevent a zero total from entering the coverage discussion as a fact. This prevents erroneous conclusions about pipeline health. Before accepting a zero total in a weekly review, inspect the source data for null amounts. Confirm whether the deals have recorded values in the amount field. Use the min_count parameter to control this empty sum behavior. This step clarifies whether the pipeline is truly empty or merely unpopulated.

Conditional division remains unsafe

Do not assume that a CASE expression protects every division in your pipeline calculation. PostgreSQL documentation states that conditional evaluation has exceptions because subexpressions of an expression are evaluated at different times, so the principle that “CASE evaluates only necessary subexpressions” is not ironclad according to PostgreSQL. This timing variance means that logic guarded by a condition may still be processed before the run-time check occurs. Specifically, a constant 1/0 subexpression will usually result in a division-by-zero failure at planning time, even if it is within a CASE arm that would never be entered at run time according to PostgreSQL. If your report query includes a hardcoded zero divisor or a constant that resolves to zero during the planning phase, the query can fail before any row-level conditions are applied. Inspect your SQL for constant divisions that rely on CASE to prevent execution errors.

To mitigate this risk in calculation review, inspect the use of NULLIF as part of the denominator logic. The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1 according to PostgreSQL. However, NULLIF alone does not make an entire pipeline calculation correct; it only addresses the specific case where two arguments are equal. You must still verify that other parts of the expression do not contain constant divisions that trigger planning failures. Check each division operation in your report’s underlying query, and do not treat NULLIF as protection for a separate constant subexpression.

Date comparisons shift when rows are missing

In the Offset documentation, the value in the Previous column for October 4th is 3, which is the value of the previous row, according to Metabase. While correct based on the data, the previous row is October 2nd; there’s no data for the previous day, October 3rd, according to Metabase. This gap creates a misleading total because the comparison skips a full day without warning. If you want to use offset to compare previous days (or weeks, or whatever), you need to make sure that your data includes a row for each data point you want to compare, according to Metabase. In this case, your data would need to include a row for each day, including days where the count was zero, according to Metabase.

Missing dates distort averages and trends used in coverage discussions. To fix this, if you’re missing dates in your data, you could join a calendar table to your data to make sure that each day has a row in your data, according to Metabase. This approach ensures every date in the range exists as a record before the offset calculation runs.

Inspect your date series for gaps before relying on period-over-period metrics. Read Close date change log: why the date moved if a date gap is in play. Verify that your dataset contains entries for every single day in the reporting window, even when activity is zero. If your source system does not generate rows for inactive days, introduce a calendar table join to fill the voids. Check the specific offset values in your query to confirm they align with calendar days rather than available records. Review any averaging expressions and compare them with the rows the offset actually selects. Validate the final output against a known date range to catch skipped days early. Use this verification step whenever a trend line shows an unexpected jump or dip that does not match reported sales activity.

Illustrative example of a skipped day

Suppose daily counts are 3 on day 2 and 0 on day 4, with no row for day 3. The previous row on day 4 is day 2, so an offset returns 3. Add a day 3 row with a count of 0. The previous row on day 4 is then that 0.

Refresh timing gets assumed as completion

Treat the scheduled refresh slot as a start time, not a confirmation that data is current. The documentation for "Configure scheduled refresh - Power BI | Microsoft Learn" sets the target to initiate the refresh within 15 minutes of the scheduled time slot, but notes that a delay of up to one hour can occur if the service can't allocate the required resources sooner, according to Microsoft Learn. This gap between the planned slot and actual initiation means the report may display stale figures even after the expected refresh window has passed. If your pipeline total looks off during this period, check the refresh status directly rather than assuming the data updated on schedule. Use Reconcile sales dashboard totals with 4 checks with the refresh status check.

Additionally, scheduled refresh is automatically paused after a period of inactivity, according to Microsoft Learn. Before discussing coverage based on these numbers, verify whether the refresh is currently active or paused due to inactivity. Confirming the refresh state prevents you from presenting outdated totals as current pipeline value.

Publisher reference table for investigation

The following table maps each publisher to the specific documented behavior that can alter a pipeline report total. Use this reference to trace which underlying mechanism—missing inputs, conditional evaluation, date offsets, or refresh timing—is responsible for a discrepancy you observe in your coverage data.

PublisherDocumented BehaviorSource Page Title
pandasBy default, the sum of an empty or all-NA Series is 0.pandas.DataFrame.sum — pandas 3.0.6 documentation
PostgreSQLSubexpressions are evaluated at different times, so “CASE evaluates only necessary subexpressions” is not ironclad.PostgreSQL: Documentation: 18: 9.18. Conditional Expressions
MetabaseThe value in the Previous column for October 4th is 3, which is the value of the previous row.Offset - Metabase Documentation
Microsoft LearnThe target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can't allocate the required resources sooner.Configure scheduled refresh - Power BI - Microsoft Learn

Check four items today

Before the coverage conversation, open one suspicious total and check four items: an empty or all-NA amount series, a constant 1/0 division, a missing daily row, and whether scheduled refresh is active or paused.

Related guides: Conditional pipeline sums: 4 mistakes that alter totals · Pipeline count inflation: 6 mistakes behind the total · Pipeline report filters: 4 tools for coverage reviews.

FAQ: tracing misleading pipeline report totals

What causes pipeline report errors in calculated totals?

Four documented behaviors create misleading numbers. Missing amounts can sum to zero, conditional division may fail during planning, missing date rows shift comparisons, and refresh timing is often assumed as completion. Each requires a specific check before the total is used in coverage discussions.

Does the zero-total behavior apply if my report uses pandas?

Yes, pandas defaults to a sum of 0 for an empty or all-NA Series according to pandas. Inspect your calculation logic to see if missing inputs are being treated as valid zeros.

Can PostgreSQL CASE still produce a division-by-zero failure?

A constant subexpression like 1/0 will usually result in a division-by-zero failure at planning time, even if it is within a CASE arm that would never be entered at run time according to PostgreSQL. Do not assume that wrapping a division in CASE protects every path in the query plan.

How does Metabase compare dates when a daily row is missing?

The offset function compares the previous row, not necessarily the previous calendar day according to Metabase. Check date completeness and consider joining a calendar table to ensure gaps do not skew the comparison.

When can a Power BI scheduled refresh start later than expected?

The target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service cannot allocate the required resources sooner according to Microsoft Learn. Automatic pauses also occur after periods of inactivity. Verify the actual refresh status rather than treating the scheduled slot as proof of completion.

Sources