3

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

Improve this question

2 Answers 2

Reset to default
0

Because the second table contain duplicated values.

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

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

Improve this answer
3
  • 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
    Commented Mar 14, 2018 at 6:47
  • 1
    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
    Commented Mar 14, 2018 at 8:08
  • Thank you so much for this, Jeffery. I thought I was losing my mind and had almost given it up as a lost cause.
    – cutts
    Commented Feb 28, 2020 at 11:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .