Pivot Table Issues

Troubleshoot Incorrect Drill-Down Values in an Excel Power Pivot Table

Adam DavisAdam Davis Nov 14, 2026 868 views

Question details

Determine why a Power Pivot drill-down result differs from a displayed PivotTable total and establish the correct reporting rules.

Troubleshoot Incorrect Drill-Down Values in an Excel Power Pivot Table
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A PivotTable reports submission and status counts using a data model and a calendar slicer.
Observed behavior
The drill-down result does not match the PivotTable total, and the calendar table used by the slicer has no relationship to the fact table.
Before you start

Identify the PivotTable cell, measure, and slicer selection that produce the mismatch. Open Power Pivot > Manage to inspect the calendar and fact tables, and confirm which date columns each report should use.

Solution 1Recommended

Define the reporting rules and validate the date model

Use this approach before changing DAX so that each measure counts the intended records under the intended date filters.

1
Document each count

List the rules for the PivotTable measures: planned submissions by planned date when an actual submission date exists, actual submissions by actual date, and responses when an approval date exists. Specify the date used for responses and whether counts represent rows, drawings, or unique submittals.

2
Clarify exclusions and statuses

Write down how to identify an earlier resubmission and when action levels L1 and L5 exclude it from the forecast count. Map each action level to the approval, rejection, or review category used in the stacked chart.

3
Inspect relationships

In Excel, select Power Pivot > Manage, then open Diagram View. Check whether the calendar date is related to the appropriate fact-table date and whether the calendar has a unique date column. An unrelated calendar slicer will not filter the fact table through a relationship.

4
Choose the date-filter approach

Use Design > Manage Relationships in the Power Pivot window to review or create an appropriate relationship. If measures use different date columns, plan for the relevant inactive relationship to be activated in a measure with USERELATIONSHIP, or use another deliberate date-filtering design.

5
Specify the previous-week comparison

Choose a date in the calendar slicer and define the previous week's start and end dates, including whether the comparison uses a calendar week or the preceding seven days. Apply that definition when reviewing the DAX measure.

Disconnected slicers can be intentional: A disconnected calendar can work if the DAX measure explicitly applies its selection to the fact table. Do not add a relationship until you understand the intended filter behavior.
Free Microsoft Office alternative

Review Spreadsheet Data with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review source data and make migration easier; Power Pivot model and DAX troubleshooting still requires Microsoft Excel.

Review and filter spreadsheet source data in a familiar interfaceCompatible with common Excel formats, including XLSX and XLSFree and lightweight for everyday spreadsheet workFamiliar tools help users move from Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my calendar slicer filter the Power Pivot fact table?

The calendar may be disconnected from the fact table. Check Power Pivot > Manage > Diagram View for a relationship, and inspect the measure for explicit logic that applies the slicer selection.

Why can drill-down rows differ from a PivotTable measure?

Drill-down displays underlying records, while a DAX measure may apply additional filters, use a distinct count, or change filter context. Compare the measure in Power Pivot > Manage with the rows opened by double-clicking the PivotTable value.

How can one calendar filter different submission dates?

Review the relationships in Power Pivot > Manage > Diagram View. Depending on the model, a measure can activate an appropriate inactive relationship with USERELATIONSHIP, or the model can use another intentional date-filtering design.

What should I define before calculating the previous week?

Decide whether the selected slicer date refers to a calendar week or a rolling seven-day period, and specify the start and end dates. Then check that the DAX measure uses the appropriate fact-table date.