PivotTables have traditionally been constructed using OLAP cubes and other complex data sources that already have rich connections between tables. However, in Excel, you are free to import multiple tables and build your own connections between tables. While this flexibility is powerful, it also makes it easy to bring together data that is not related, leading to strange results.
Have you ever created a PivotTable like this? You intended to create a breakdown of purchases by region, and so you dropped a purchase amount field into the Values area, and dropped a sales region field into the Column Labels area. But the results are wrong.
How can you fix this?
The problem is that the fields you have added to the PivotTable might be in the same workbook, but the tables that contain each column are not related. For example, you might have a table that lists each sales region, and another table that lists purchases for all regions. To create the PivotTable and get the correct results, you need to create a relationship between the two tables.
After you create the relationship, the PivotTable combines the data from the purchases table with the list of regions correctly, and the results look like this:
Excel contains technology developed by Microsoft Research (MSR) for automatically detecting and fixing relationship problems like this one.
Using Automatic Detection
Automatic detection checks new fields that you add to a workbook that contains a PivotTable. If the new field is unrelated to the column and row headers of the PivotTable, a message appears in the notification area at the top of the PivotTable letting you know that a relationship may be needed. Excel will also analyze the new data to find potential relationships.
You can continue to ignore the message and work with the PivotTable; however, if you click Create, the algorithm goes to work and analyzes your data. Depending on the values in the new data and the size and complexity of the PivotTable, and the relationships that you have already created, this process can take up to several minutes.
The process consists of two phases:
-
Detection of relationships. You can review the list of suggested relationships when analysis is complete. If you do not cancel, Excel will automatically proceed to the next step of creating the relationships.
-
Creation of relationships. After the relationships have been applied, a confirmation dialog appears, and you can click the Details link to see a list of the relationships that have been created.
You can cancel the detection process, but you cannot cancel the creation process.
The MSR algorithm searches for the “best possible” set of relationships to connect the tables in your model. The algorithm detects all possible relationships for the new data, taking into consideration column names, the data types of columns, the values within columns, and the columns that are in PivotTables.
Excel then chooses the relationship with the highest ‘quality’ score, as determined by internal heuristics. For more information, see Relationships Overview and Troubleshoot Relationships.
If automatic detection does not give you the correct results, you can edit relationships, delete them, or create new ones manually. For more information, see Create a Relationship Between Two Tables or Create relationships in Diagram View
Blank Rows in Pivot Tables (Unknown Member)
Because a PivotTable brings together related data tables, if any table contains data that cannot be related by a key or by a matching value, that data must be handled somehow. In multidimensional databases, the way to handle mismatched data is by assigning all the rows that have no matching value to the Unknown member. In a PivotTable, the unknown member shows up as a blank heading.
For example, if you create a Pivot Table that is supposed to group sales by store, but some records in the sales table do not have a store name listed, all records without a valid store name are grouped together.
If you end up with blank rows, you have two choices. You can either define a table relationship that works, perhaps by creating a chain of relationships among multiple tables, or you can remove fields from the PivotTable that cause the blank rows to occur.
