Reconcile a Power BI total with a source SQL query

To reconcile a Power BI measure with SQL, make both calculations answer the same question over the same source snapshot. Match eligibility, grain, filters, units and time interpretation before investigating the arithmetic.

A total mismatch often comes from a different population rather than a different SUM function. An equal total is useful evidence, but group and record-level checks are still needed to detect offsetting errors.

State the measure contract

The retail lab defines Paid net invoice-line value as Quantity × UnitPricePaise − DiscountPaise for Status=Paid. Amounts are integer paise; discount applies once per line. OrderDate is the active reporting date.

The unfiltered paid population contains seven lines across six orders. One additional Pending line belongs outside this measure. The explicit Unknown customer member remains included.

Run the supplied Python builder to create its in-memory SQLite database and reference outputs. The following SQL blocks execute against the lab's FactSales table.

Establish the unfiltered source control

SELECT COUNT(*) AS paid_lines,
       COUNT(DISTINCT OrderID) AS paid_orders,
       SUM(Quantity * UnitPricePaise - DiscountPaise) AS paid_net_paise
FROM FactSales
WHERE Status = 'Paid';

Expect seven lines, six orders and 69,500 paise. Compare that with the Power BI Paid Net Paise measure under no additional report filters and the intended consumer security context.

If the model displays rupees, compare 695 rather than 69,500. A unit mismatch is not a calculation discrepancy. Keep the raw-unit control available so presentation rounding does not hide differences.

Match the selected period

SELECT SUM(Quantity * UnitPricePaise - DiscountPaise) AS january_paid_paise
FROM FactSales
WHERE Status = 'Paid'
  AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01';

Expect 47,500. Select January 2026 through DimDate in Power BI and compare. If the total remains 69,500, investigate the date relationship, date types and measure filter behaviour.

Confirm that the report uses OrderDate rather than DeliveryDate. Two correctly written queries using different date roles can disagree legitimately.

Compare category slices

SELECT p.Category,
       SUM(s.Quantity * s.UnitPricePaise - s.DiscountPaise) AS paid_net_paise
FROM FactSales AS s
JOIN DimProduct AS p ON p.ProductKey = s.ProductKey
WHERE s.Status = 'Paid'
GROUP BY p.Category ORDER BY p.Category;

Expect Software 39,000, Support 7,000 and Training 23,500. The dimension key is unique in the clean fixture, so the join preserves line grain. A duplicate product key would invalidate that assumption and could inflate the SQL comparison itself.

The star-schema lesson defines the corresponding semantic model. Microsoft describes relationship behaviour in its Power BI model documentation.

Inspect exact contributing records

SELECT LineID, OrderID, Quantity * UnitPricePaise - DiscountPaise AS net_paise
FROM FactSales
WHERE Status = 'Paid' AND ProductKey = 1
  AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'
ORDER BY LineID;

January Software contains S1 at 19,000 and S3 at 10,000. Compare the detail-key set with a Power BI table under the same context. If a measure total is correct but the detail differs, inspect visual eligibility and drill-through filters.

For a restricted consumer, apply the equivalent allowed population to the source check. A model viewed under North RLS should not be compared with unrestricted SQL and labelled wrong merely because it excludes West.

Reconcile the snapshot and refresh boundary

Freeze the source input or record extraction and refresh times. If SQL reads a corrected source while the model retains an older imported snapshot, the discrepancy may be freshness rather than DAX.

Record late-arriving and historical corrections, then rerun affected controls after refresh or backfill. Do not manually adjust a dashboard measure solely to match a newer source total without understanding which records changed.

The article's SQL executes in SQLite and its results are recorded in the programme's QA output. DAX and visual results require actual Power BI review; the source checks do not claim those have been run.

Exercise: exclude Unknown customer key 0 from one side only. Explain the 5,000-paise discrepancy, then restore matching eligibility. Next introduce a duplicated dimension key and show why the SQL control also needs key-quality checks.

NeuraPath's Data Analytics with Generative AI course connects SQL and Power BI through shared definitions. A strong reconciliation explains the population and exact records behind every matched total.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Diagnose a slow Power BI page with performance evidence.
• Continue with Design a stakeholder dashboard acceptance checklist.