Search code examples
excelpowerpivot

Power Pivot: pivot table shows every city for related tables

Can someone help me figure out why all three towns are displayed in the Pivot Table (rows 8 through 17)?

Blue table is connected to black table by customer name. The output should show only customers B and C with their respective towns, however I end up seeing B and C, with every town that's referenced in the blue table. enter image description here

enter image description here

Solution

  • I recreated your problem. When I don't have a "properly" relationship between the tables you can get weird behaviour. The behaviour can occur when you don't have the tables linked, but that's not your case.

    So how is your link in the Power Pivot Manager? The table to the right will be used as your source table with your unique primary key and the other table will "feed" your source table. So it depends how you want to analyze the data.

    My looks like the following, and here is the problem.. your dependency goes from tLocal -> tData. It should be tData -> tLocal:

    View below is: The pivot table to the left is "tLocal -> tData", to the right is "tData -> tLocal"

    enter image description here

    View of the relationship table:

    enter image description here

    In the "Manage Relationshops" table your can modify your existing setup (you can see mine in the 2nd window, and choose "Create" a new).