When two correct systems show different numbers
Few analytics problems damage trust faster than a Salesforce report showing one number while the executive BI dashboard shows another. The immediate reaction is usually that one system must be wrong. Sometimes it is. More often, the two reports are answering subtly different questions.
A CRM report may evaluate live Salesforce records using Salesforce formulas, report filters and relationship logic. A BI dashboard may query a replicated dataset, transform it through several models, join it to other systems and refresh on a schedule. Even when both display a metric called “Active Customers,” they can differ in population, timing, grain and calculation.
The right response is not to adjust the BI calculation until the totals happen to match. That creates fragile reporting. The objective is to identify exactly why the numbers differ, decide which definition the business intends to use, and make that definition reproducible.
Actiknow’s Salesforce services include Salesforce integration with other systems, while its business intelligence work covers dashboards and integrations across databases and APIs. Those are separate layers of the same reconciliation problem: the CRM is a source of operational truth, while the analytical model determines how that source is interpreted for reporting.
1. Begin with the exact business question
Before comparing SQL with a Salesforce report, write the metric as a sentence. “Count active members” is insufficient. A useful definition might specify which membership types qualify, what active means, whether one person with two memberships counts once or twice, which date determines activity, whether test records are excluded and what time zone applies.
Then capture the Salesforce report configuration: report type, filters, cross filters, date field, date range, grouping, unique-count settings, bucket fields and any dashboard-level filters. Save a representative record list, not just the total. A total of 5,000 versus 5,012 tells you that twelve records differ. The identities of those records tell you why.
This is the most important practical technique in reconciliation: compare populations, not only aggregates.
2. Check whether you are counting the same entity
Salesforce data is relational. A contact can have multiple opportunities, affiliations, applications, cases or campaign memberships. A BI query that joins Contact to Opportunity can multiply a contact into several rows. COUNT(*) may therefore answer “How many joined rows exist?” while a Salesforce unique count answers “How many contacts qualify?”
Decide the grain before writing the metric. Is one row supposed to represent a contact, account, opportunity, membership, invoice or transaction? Then test uniqueness at that grain before adding joins.
For example, if the intended metric is accounts with qualifying opportunities, COUNT(DISTINCT account_id) may be appropriate. If the metric is qualifying opportunities, distinct account count would be wrong. Neither expression is universally correct. The business definition determines the grain.
A useful diagnostic is to group by the intended primary key and find keys appearing more than once after each join. This quickly exposes one-to-many relationships that inflate totals.
3. Reproduce every filter, including the invisible ones
Visible Salesforce report filters are only part of the story. Differences can come from record type, ownership scope, status, account type, deleted records, null handling, user permissions and report-type relationship rules.
SQL also treats nulls in ways that surprise people. A condition such as status <> ‘Cancelled’ does not automatically include rows where status is null. If Salesforce logic effectively includes blank status, the SQL may need an explicit OR status IS NULL.
Text filters deserve similar care. Case sensitivity, trailing spaces, blank strings and test-record conventions can produce small but persistent differences. Instead of guessing, take records present in Salesforce but absent in BI and records present in BI but absent in Salesforce. Inspect the fields used by every filter. Patterns usually emerge quickly.
4. Formula fields are a frequent source of mismatch
A replicated Salesforce object may contain the fields used by a formula without reproducing the formula’s evaluated result exactly. Formula fields can reference related objects, TODAY(), conditional branches, text transformations, picklists and other formulas. Their value can also change without a conventional update to the underlying record when the formula depends on the current date.
If a Salesforce dashboard relies on a formula, document the formula itself and decide how the warehouse will represent it. Options include ingesting a usable evaluated field where the connector supports it, recreating the logic in the transformation layer, or defining a warehouse-native governed metric that intentionally replaces the Salesforce calculation.
Do not casually translate a long Salesforce formula into a one-off CASE expression inside a dashboard. Centralize reusable business logic, test it against known records and keep the mapping version-controlled. If the Salesforce formula changes, the analytical implementation needs a controlled way to change with it.
5. Dates, time zones and “today” can move records across boundaries
A record created at 11:30 PM in one time zone may fall on the next calendar date in another. Salesforce reports can use user or organization settings, while a warehouse may store UTC timestamps and a BI model may apply another local conversion.
This matters for daily sales, month-end reporting, membership expiration, aging and any metric involving TODAY() or relative date filters.
Document four things for every important date metric: source timestamp and its zone, warehouse storage convention, business reporting zone, and the exact boundary used for date filtering. Test records around midnight, daylight-saving transitions where relevant, month-end and year-end.
For date-based status, decide when the metric is supposed to be evaluated. “Active as of now” and “active at the end of last month” require different logic. A live Salesforce formula evaluated today cannot reconstruct a historical snapshot unless the required historical state was stored.
6. Refresh timing creates legitimate temporary differences
Salesforce is operational and can change continuously. A warehouse may ingest every few minutes, hourly or daily. Transformation jobs and BI semantic models can introduce additional delay. A salesperson changing an opportunity at 10:03 may see the new value immediately in Salesforce and the old value in BI until the pipeline completes.
Make freshness visible. Record source extraction time, transformation completion time and dashboard refresh time where practical. Define a reasonable reconciliation window. If the systems are compared before the agreed data-latency window has passed, a mismatch is not necessarily a defect.
This is also why “real time” should be treated as a business requirement, not a default architecture choice. The useful question is how quickly a decision needs the change, and what reliability and cost are justified by that latency.
7. Deleted and changed records need explicit handling
CRM replication is not just a sequence of inserts. Records can be updated, deleted, undeleted or merged. Connectors may represent deletion through flags, history tables or change events. A warehouse model that ignores deletion semantics can retain records that Salesforce no longer includes.
Similarly, incremental pipelines can miss changes if the watermark logic is wrong, if late-arriving data falls outside the extraction window or if an upstream schema change interrupts processing.
For high-value metrics, test the pipeline itself. Compare source record counts by modification date, inspect deletion behavior, monitor freshness and row volume, and maintain a controlled reprocessing strategy. Actiknow’s business intelligence offering explicitly includes integration from databases and APIs, which is why reconciliation should cover the data movement layer as well as the final visualization.
8. Security can make two users see different truths
Salesforce sharing rules and BI row-level security can intentionally restrict records. Two users may run what appears to be the same report and receive different totals because their accessible populations differ.
Reconciliation therefore needs a known comparison identity. Confirm whether the Salesforce report is being run as a particular user, whether the extraction account can see the same population, and what BI security filters apply to the viewer.
Do not “fix” a security-driven difference by broadening access merely to make numbers match. The correct outcome may be different totals for different roles, provided that behavior is intentional and documented.
9. Currency and derived financial measures require special care
Salesforce organizations using multiple currencies can introduce corporate currency, record currency, conversion rates and dated exchange rates. Finance reporting may use yet another approved rate or accounting treatment.
If pipeline or revenue totals differ, compare the raw amount, currency code, conversion rate, conversion date and reporting currency before inspecting dashboard visuals. Also determine whether the metric uses booked, billed, recognized or collected revenue. A label saying “Revenue” can conceal several valid definitions.
10. Build a reconciliation harness instead of debugging manually every month
Once a metric matters to leadership, reconciliation should become repeatable. A practical control framework can include source-versus-warehouse counts, distinct-key counts, sums of important measures, null rates, duplicate checks, freshness thresholds and a small set of record-level comparisons.
Store the result of each check. Alert on material deviations. This turns reconciliation from a launch activity into an operating control.
A useful pattern is to maintain three layers. First, source-aligned staging models preserve the meaning of replicated Salesforce objects. Second, governed transformation models implement documented business rules. Third, reporting models expose metrics at the grain needed by dashboards. Keeping these responsibilities separate makes discrepancies much easier to trace.
A step-by-step reconciliation process
- Step 1: Freeze the comparison. Record the Salesforce report, filters, viewer, run time and BI refresh time.
- Step 2: Define the metric. State the population, grain, formula, exclusions, date rule and security context.
- Step 3: Export record identifiers from both sides. Create Salesforce-only and BI-only exception sets.
- Step 4: Trace a small sample. For each exception, inspect filters, relationships, formulas, dates, deletion state and permissions.
- Step 5: Classify the cause. Common categories are definition mismatch, join multiplication, formula mismatch, latency, null behavior, security, currency, historical-state limitation or pipeline defect.
- Step 6: Fix the correct layer. Source replication problems belong in ingestion; reusable business logic belongs in transformation; presentation logic belongs in the semantic or reporting layer.
- Step 7: Retest totals and exceptions. A matching total is not enough if different errors cancel each other out. The populations should reconcile at the intended grain.
- Step 8: Automate the control. Add tests and monitoring so the same class of mismatch does not quietly return.
What executives should ask when dashboards disagree
Executives do not need to debug SQL, but they should insist on disciplined answers. Ask: Are the two reports measuring the same population? Are they current to the same point in time? Is the metric definition documented? Can the team identify the specific records causing the difference? Is the discrepancy expected, temporary or defective? Which system owns the definition? What automated control will detect recurrence?
A data team that can answer those questions with record-level evidence is in a much stronger position than one that simply tweaks formulas until totals align.
Frequently asked questions
Why does Power BI show a different number from Salesforce?
Power BI may use replicated Salesforce data with different refresh timing, filters, joins, formulas, security or date logic. Start by comparing the exact record identifiers included by each report, then trace the exceptions.
Should Power BI always match Salesforce exactly?
Not necessarily. It should match when both are intentionally measuring the same definition, population and point in time. A warehouse may deliberately use a governed cross-system definition that differs from an operational Salesforce report. That difference should be documented rather than hidden.
How do duplicate joins affect Salesforce reporting in a warehouse?
Joining a parent record to multiple child records can multiply rows. If the intended metric counts parents, validate uniqueness or use the appropriate distinct key. Do not use DISTINCT as a blanket fix without understanding the intended grain.
Can Salesforce formula fields cause warehouse discrepancies?
Yes. Formulas can depend on related fields, current dates and Salesforce-specific logic. Recreate important formulas centrally and test them against Salesforce records, or explicitly define a different governed warehouse metric.
How should we handle Salesforce report filters in BI?
Document every relevant condition, including blanks, record types, status, dates, ownership and security context. Translate the business meaning rather than copying filter labels mechanically.
What is the fastest way to diagnose a mismatch?
Export the identifiers behind both totals and compare the two sets. Investigating records that appear on only one side is usually faster and more reliable than staring at aggregate SQL.
How can we prevent the problem from recurring?
Use governed metric definitions, tested transformation logic, pipeline monitoring and automated source-to-warehouse reconciliation checks for critical metrics.
Turn disagreement into a data-quality control
A Salesforce-versus-BI discrepancy is frustrating, but it is also useful evidence. It reveals where business definitions, source behavior and analytical logic are not yet fully aligned. Solving the discrepancy at record level creates something more valuable than one matching dashboard: a repeatable method for trusting future metrics.
If your organization is spending too much time reconciling Salesforce reports with management dashboards, review Actiknow’s Salesforce integration capabilities and business intelligence services. For a focused discussion about the data model, pipeline and reconciliation controls behind your reporting, contact the Actiknow team.