Pivot Table Displaying Repeat Values in Columns

NgothoKariuki

New Member
Joined
Feb 27, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Table 1 contains print list with user names (below)
Screenshot 2023-02-27 at 20.04.25.png

Table 2 contains user names and departments(below)
Screenshot 2023-02-27 at 20.05.06.png

Created a model and related the two tables using username in both tables(below)
Screenshot 2023-02-27 at 20.06.05.png

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!

Screenshot 2023-02-27 at 20.09.23.png


Please help
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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.
 
Upvote 0
That doesn’t answer my question, I’m afraid, and I still think your relationship is probably the wrong way round.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,577
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top