Fix Repeated Store Row Labels in an Excel Power Pivot Table
Question details
Show only the stores associated with each region 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.
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.
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.
Select the PivotTable, then open Power Pivot > Measures > New Measure. Choose the Stores table and enter a name such as Store Count.
In the formula box, enter =COUNTROWS(Stores), replacing Stores with the actual table name if it differs. Click OK.
In the PivotTable Fields pane, drag Store Count to the Values area. Keep the region field above the store field in the Rows area.
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.

Check the Data Model relationship and empty-item settings
Use these checks if adding a measure does not remove unrelated stores.
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.

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.




