Pivot Table Issues

Why Excel Power Pivot Drill-Down Rows Differ from a CALCULATE Measure

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Understand why the number of rows shown by a Power Pivot drill-down differs from a CALCULATE measure's result.

Why Excel Power Pivot Drill-Down Rows Differ from a CALCULATE Measure
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A user compares a PivotTable measure with the source records displayed by drilling down into a selected item.
Observed behavior
The drill-down displays 22 source rows for item 239K01, while the measure returns an aggregate value of 25.
Before you start

Identify the PivotTable cell you are investigating and locate the measure's DAX formula in Power Pivot. Note the active row, column, slicer, and report filters before comparing results.

Solution 1Recommended

Compare the drill-down context with the measure's DAX

Use this method to determine whether the drill-down rows and the measure are counting the same data under the same filters.

Drill-down shows underlying source rows associated with the selected PivotTable context. It does not necessarily reproduce filters applied inside a measure by CALCULATE or FILTER.

A result of 25 and a drill-down of 22 do not by themselves prove an error. Check what the measure aggregates, which table it uses, and whether its formula changes the filter context.

1
Inspect the source rows

Double-click the PivotTable value for item 239K01 to open its drill-down sheet. Check which table and records are represented by the 22 displayed rows.

2
Review the measure

Open Power Pivot > Manage and find the measure in the calculation area. Inspect its CALCULATE expression, filter arguments, and aggregation to see whether it counts rows, counts values, or sums a column.

3
Check relationships and filters

In the Power Pivot window, open Diagram View to inspect relationships between the PivotTable fields and the measure's table. Also check the PivotTable's slicers and report, row, and column filters.

4
Compare a row-count measure

In the Power Pivot calculation area, add a measure such as Source Rows := COUNTROWS('SourceTable'), replacing 'SourceTable' with the relevant table name. Add it to the PivotTable Values area and compare it with the original measure for the same item.

Compare the drill-down context with the measure's DAX
Interpret the comparison: COUNTROWS counts rows in the named table under its current filter context. If the original measure modifies filters, aggregates a different table, or sums values, its result can differ from both this count and the drill-down row count.
Free Microsoft Office alternative

Explore PivotTable Data with WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheet data. Open compatible Excel files and use spreadsheet tools as you migrate your everyday work. WPS does not fix Excel Power Pivot behavior, and advanced data-model features may not be fully supported.

Review and analyze spreadsheet data with familiar PivotTable toolsOpen and save compatible Microsoft Excel XLSX and XLS filesUse a free, lightweight office suite with a familiar interfaceMove everyday spreadsheet work to WPS with less relearning
microsoft office alternative - wps office

Frequently Asked Questions

Does drill-down show only the rows included by CALCULATE?

Not necessarily. Double-clicking a PivotTable value shows underlying rows associated with the selected context, but filters applied inside the measure may not be reflected in the drill-down list.

Why can a measure return 25 when drill-down shows 22 rows?

The measure may sum values, aggregate a different table, or change filters through CALCULATE. Open Power Pivot > Manage and inspect the formula and relationships before comparing the numbers.

How can I count source rows in the PivotTable context?

In the Power Pivot calculation area, create a measure such as Source Rows := COUNTROWS('SourceTable'), using your actual table name. Add the measure to the PivotTable Values area and compare it for the same item.

Does a difference mean FILTER or CALCULATE is broken?

No. A difference alone does not establish a malfunction. Check the measure's aggregation and filter arguments, the tables involved, and the PivotTable filters to determine what each result represents.