How to Display Related Rows from a Power Pivot Relationship in Excel/WPS Spreadsheet
Question details
The user needs to display detail rows in a PivotTable generated from two related tables within a Power Pivot Data Model.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating and analyzing a PivotTable based on multiple related tables in a data model.
- Observed behavior
- The PivotTable displays no detail rows or related records when the Values area is left empty, even though a correct relationship exists between the tables.
Verify that your tables are correctly linked with primary and foreign keys in the Data Model manager before modifying your PivotTable configuration.
Add a Numeric Field to the Values Area
To force the PivotTable to evaluate and display the related records from the data model, you must include at least one field in the Values area.
A PivotTable relies on mathematical calculations or data aggregations to render rows. When working with related tables in a Power Pivot Data Model, simply adding fields to the Rows or Columns areas is not enough. The engine requires a measurement to establish the context of the related data.
Click anywhere inside your existing PivotTable to make the PivotTable Fields pane appear on the right side of your screen.
In the PivotTable Fields pane, find a relevant numeric field, such as an ID, quantity, or sales figure, from one of your related tables.
Click and drag the chosen field into the 'Values' box at the bottom right of the pane.
Check your PivotTable. The related detail rows should now populate as the PivotTable calculates the intersections of the related data.
Easily Analyze Related Data with WPS Spreadsheet
WPS Spreadsheet provides robust PivotTable functionality, allowing you to quickly connect, summarize, and analyze related datasets without complex configurations.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the tables you want to analyze.
- 2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon and click the 'PivotTable' icon.
- 3. Configure your layout: Use the PivotTable pane on the right to drag row and column headers into their respective areas.
- 4. Add a Value field: Drag at least one numeric identifier into the 'Values' area to ensure related records are accurately populated and displayed.
Frequently Asked Questions
Why is my PivotTable blank even though the tables are linked?
A PivotTable needs a metric to perform calculations. If the 'Values' area in the PivotTable Fields pane is empty, it lacks the context to calculate and display related rows, even if the table relationship is perfectly valid.
Can I use a text field in the Values area to show related rows?
Yes, but the PivotTable cannot sum text. If you drag a text field into the Values area, it will default to 'Count', which is sufficient to trigger the display of related detail rows.
How do I check if my table relationships are set up correctly?
Go to the Data tab and open the Relationships manager. Verify that the foreign key of your data table is correctly mapped to the primary key (unique identifier) of your lookup table.
