Why Power BI Disagrees
With Your Excel Report

The new dashboard says revenue was 4.2 million. The spreadsheet finance has used for six years says 4.35 million. Everyone in the room now trusts the spreadsheet, and the dashboard has about one more chance before it gets abandoned.

In nearly every case I have investigated, the dashboard is not broken. It is answering a slightly different question. Here are the four causes, in the order they turn up, and how to tell which one you have.

Start By Narrowing It

Before diagnosing, find out whether the gap is everywhere or somewhere. Filter both to a single month, then a single customer, then a single transaction. The level at which they start agreeing tells you which cause you are looking at.

Cause 1: Rows Dropping Through a Relationship

A sales row whose customer key does not exist in the customer table still appears in a raw total, but disappears the moment you slice by customer name. Excel, doing a flat SUM, keeps it. Power BI, filtering through a relationship, drops it.

Power BI collects these into a blank row on the dimension side, and because most report designers filter blanks out for tidiness, the amount vanishes silently.

Find the orphaned amount

Orphaned Revenue =
CALCULATE(
    SUM( Sales[Amount] ),
    ISBLANK( RELATED( Customer[CustomerName] ) )
)

// If this returns a non-zero value, that is your gap.

The fix is upstream, not in DAX. Either the keys are genuinely missing from the dimension, in which case the source needs correcting, or the join is on the wrong column. Patching it with a DAX workaround hides a data problem that will resurface.

Cause 2: A Ratio Computed Per Row

This is the most common and the most convincing, because the detail rows are all correct and only the total is wrong.

Someone creates margin percent as a calculated column, computing it per row. Then the total shows the average of those percentages, which is not the margin of the total unless every row happens to be the same size.

Wrong: a per-row calculated column

// Calculated column on the Sales table
Margin % = DIVIDE( Sales[Profit], Sales[Revenue] )

// Totals now show AVERAGE(Margin %) across rows.
// A 1,000,000 sale at 5% and a 10 sale at 90%
// average to 47.5%. The real margin is 5.0%.

Right: a measure that aggregates first, then divides

Margin % =
DIVIDE(
    SUM( Sales[Profit] ),
    SUM( Sales[Revenue] )
)

// Correct at every level, because the division
// happens after aggregation, in whatever filter
// context the user is looking at.

The rule generalises: anything that is a rate, ratio or percentage must aggregate its components first and divide afterwards. If it is stored per row, the total will be wrong and it will look plausible.

Cause 3: The Date Table Does Not Cover Everything

Time intelligence in DAX requires a date table marked as such, containing a continuous unbroken range of dates. Where it falls short, totals silently exclude whatever falls outside it.

Two versions of this. The range ends before your latest transactions, so the current month is missing rows. Or the table has gaps, which breaks functions like SAMEPERIODLASTYEAR in ways that produce a number rather than an error.

Check coverage against the fact table

// Compare these four values. Dates should extend
// at least to the maximum transaction date.

Min Fact Date  = MIN( Sales[OrderDate] )
Max Fact Date  = MAX( Sales[OrderDate] )
Min Date Table = MIN( 'Date'[Date] )
Max Date Table = MAX( 'Date'[Date] )

// And confirm there are no gaps:
Date Row Count = COUNTROWS( 'Date' )
Expected Days  = DATEDIFF( MIN('Date'[Date]), MAX('Date'[Date]), DAY ) + 1
// These two must be equal.

Cause 4: They Genuinely Mean Different Things

Often the numbers are both right and the definitions differ. The spreadsheet includes intercompany; the model excludes it. The spreadsheet counts an order when raised; the model counts it when shipped. One is gross of returns, the other net.

This is not a technical problem and no amount of DAX resolves it. Somebody has to decide which definition the business uses, write it down, and apply it in one place.

The productive move is to build a reconciliation view that bridges the two explicitly rather than arguing about which is correct:

A bridge finance can actually check

Spreadsheet total            4,350,000
  less intercompany            (95,000)
  less returns not yet posted  (42,000)
  plus late-posted orders        12,000
  ---------------------------------------
Power BI total               4,225,000

Show that once and the conversation ends. Argue about the headline figures and it recurs every month.

Cause 5: The Total Row Is Not a Sum of What You See

A subtler version of cause two, and it catches experienced developers. A measure using DISTINCTCOUNT, or any logic with a condition inside it, evaluates independently at every level. The total is not the sum of the rows above it, and it is not supposed to be.

Count distinct customers by month, and the yearly total is lower than the twelve months added together, because a customer who bought in March and June is one customer for the year and two across the months. That is correct behaviour that looks like a bug, and it is the single most common support question on a new dashboard.

This is correct, not broken

Active Customers = DISTINCTCOUNT( Sales[CustomerID] )

// Jan  420
// Feb  445
// Mar  462
// ...
// Sum of months: 5,180
// Year total:    1,340   <- correct
//
// Customers who bought in several months are
// counted once per year, not once per month.

There is no fix because nothing is wrong. What is needed is a label. Add a note to the visual saying that distinct counts do not sum across periods, and you will stop the question being asked every month by a different person.

Finding Which One You Have, Quickly

1. Compare a single transaction. If it differs, you have a definition problem — go to cause 4.
2. If single rows match, sum one customer. If that differs, check for orphaned keys — cause 1.
3. If customer totals match but the grand total does not, look for a ratio stored as a column — cause 2.
4. If one period is wrong and others are fine, check date table coverage and gaps — cause 3.

Four checks, usually under an hour, and they will identify the cause in nearly every case.

What We Would Do

When we are brought in on a dashboard nobody trusts, the first deliverable is a reconciliation that ties the new numbers to the old ones line by line, because trust is what was lost and trust is what has to be rebuilt first.

Then we fix the cause rather than the symptom: correct the model structure, move ratios into measures, build a proper date table, and get the definitions written down and agreed so the same argument does not recur in six months.