Pivot Table Issues

Fix Repeated Store Row Labels in an Excel Power Pivot Table

Huda QurayshiHuda Qurayshi Nov 20, 2026 869 views

Question details

Show only the stores associated with each region in an Excel Power Pivot table.

Fix Repeated Store Row Labels in an Excel Power Pivot Table
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
Region and store fields from related Data Model tables are placed in the PivotTable Rows area without a value measure.
Observed behavior
The PivotTable may display every store under every region instead of only the related stores.
Before you start

Check that the PivotTable uses the Data Model and identify the region key shared by the Regions and Stores tables. Confirm that the Stores table has a column you can count or use in a measure.

Solution 1Recommended

Add a store count measure to the PivotTable

Use a measure to evaluate the relationship and show the relevant region and store combinations.

Without a measure, a PivotTable using fields from different Data Model tables can display combinations that do not have matching data. A measure evaluates those combinations in filter context.

1
Create a measure

Select the PivotTable, then open Power Pivot > Measures > New Measure. Choose the Stores table and enter a name such as Store Count.

2
Enter the formula

In the formula box, enter =COUNTROWS(Stores), replacing Stores with the actual table name if it differs. Click OK.

3
Add the measure

In the PivotTable Fields pane, drag Store Count to the Values area. Keep the region field above the store field in the Rows area.

4
Check the result

Expand a region in the PivotTable and verify that only its associated stores appear. If unrelated stores remain, check the relationship and whether items with no data are enabled.

Add a store count measure to the PivotTable
Use an existing aggregation: If an appropriate store count or other measure already exists, drag it to Values instead of creating a new one.
Free Microsoft Office alternative

Explore PivotTables in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for easier migration. It does not fix Excel Power Pivot Data Model behavior.

Create and analyze PivotTables in WPS SpreadsheetsOpen and save Microsoft Excel formats such as XLSXWork in a familiar, easy-to-use spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does every store appear under every region?

When row fields come from different Data Model tables and no measure is present, Excel may display combinations without eliminating those that have no matching data. Adding a measure to Values evaluates the combinations in filter context.

What measure should I add to Values?

A store count is one option. Create a measure through Power Pivot > Measures > New Measure and use =COUNTROWS(Stores), substituting your actual table name.

What if unrelated stores still appear after I add a measure?

Open Data > Relationships to verify that the region keys are connected correctly. Also check Field Settings > Layout & Print for Show items with no data and refresh the PivotTable.

Would this happen if both row fields came from the same table?

Fields from the same table normally show combinations present in that table. The repeated combinations described here arise when fields come from related tables and no measure is used to evaluate them.