Pivot Table Displaying Repeat Values in Columns

[NgothoKariuki]

Table 1 contains print list with user names (below)

Table 2 contains user names and departments(below)

Created a model and related the two tables using username in both tables(below)

When I pivot from the model, it repeats a value for the different departments per person (below) - The User Only belongs to one department (e.g Admin or Kindergater) But here the value repeats itself across all depts!

Please help

 

[RoryA]

It looks to me as though your relationship is the wrong way round, but it would be helpful to know which fields are actually on the pivot table.

 

[NgothoKariuki]

It looks to me as though your relationship is the wrong way round, but it would be helpful to know which fields are actually on the pivot table.

Table 2 is the master and Table 1 contains the transactions.

 

[RoryA]

That doesn’t answer my question, I’m afraid, and I still think your relationship is probably the wrong way round.

 

[NgothoKariuki]

That doesn’t answer my question, I’m afraid, and I still think your relationship is probably the wrong way round.

Hi Rory, the Pivot Columns contains the columns from the Table 2 i.e. depts...kindergarten, Admin, etc..while the rows are from the Print list..it shows the number of B/W or Colored prints a member has made

 

[RoryA]

You need Table2 to be on the 1 side of the relationship since filtering doesn't work from many to 1. Delete the relationship you have and drag the field from Table 1 to Table 2 in the relationships window.

 

[NgothoKariuki]

T

You need Table2 to be on the 1 side of the relationship since filtering doesn't work from many to 1. Delete the relationship you have and drag the field from Table 1 to Table 2 in the relationships window.

Thanks...let me try that out and see how it goes

 

[NgothoKariuki]

You need Table2 to be on the 1 side of the relationship since filtering doesn't work from many to 1. Delete the relationship you have and drag the field from Table 1 to Table 2 in the relationships window.

THIS HAS WORKED!! Thank you very much

 

[RoryA]

Glad we could help.

 

[jimihen1]

Wait... how does that work? I have the same problem (like ALL the time when I try to use Power Pivot), but dragging in the opposite direction has no effect. Power Pivot determines the relationship type and direction, not I. I have Excel 365. Is this something in a different version?

Regardless, this still doesn't make sense to me. A lookup should work in either direction--that's what data relationships ARE. What am I missing? Is this a limitation of Excel pivot tables?