I have an issue when using PowerPivot in Excel 2016.

After creating a relationship between two tables, and afterwards create a pivot table from the data model, the resulting pivot ignores the relationship.

Below you can see my two tables - Table 2 with company code and name, Table 3 with Location, Location name and related company code.


As you can see in the resulting pivot it lists all locations for all companies instead of only the location that actually relates to the company.

2 tables and a pivot


Here is the link created in the data model, between the Company code in table 1 and company code in table 2.

The two tables are linked


For completeness sake, here is the Pivot Field List used in the first picture.

Pivot Field List


How do i make the pivot table respect the relation that i have created between the two tables?

Here is the example file: example.xmlx

up vote 0 down vote accepted

Because the second table contain duplicated values.

  • So what you mean is that I basically can't use PowerPivot for this task? – Nichlas H. Mar 13 at 9:32
  • Incorrect answer. – jeffreyweir Mar 14 at 2:44

Put any field from Table3 into the Values area. This will give you a count of the field, but more importantly it will force the relationships into effect.

Here's an example.

enter image description here

enter image description here

  • He don't display Values filed in Pivot Table. All files in Rows. You can try to create relationship manually in PivotTable with duplicate name. – Lee Mar 14 at 6:47
  • Your comment is nonsensical. What does the OP want? He wants the PivotTable to show the relationship. How does he do that? By putting a field in the Values area. Why didn't his image have this? Because he didn't know it would help. What does my answer do? Exactly what he wants. – jeffreyweir Mar 14 at 8:08

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.