Why doesn't this work? I want big table Acct number to small tables?

[pete234]

Big table has 10000 acct numbers (unique) and rep names (repeating) along with some calculations from the small tables.
The big table has a relationship to the small tables by account number which is unique in the Big Table which allows all the tables to find their rep, this is what the pivot is for.
The small tables are also related to fiscal table which I also linked to big table by making a report date field.
I also have a table of unique rep names related to the Big Table.
What I need to do is make a padded table of the smaller tables so it will line up with the big table for slicer charting purposes (not in pp).

If I use the Unique rep name, I can drop in say Date from a small table. But if I then try to add Acct number from big table it seems to dupe everything to 450k rows.
If I use the Big Table rep name, I can get in Acct Number and Rep but adding date from small table it goes off the page I assume duping as well.

In a nutshell I guess, I need the small tables to know that there are 10k account numbers and reps, not just the numbers in their data.
Like in an Access query, show me everything even if blank. Ideas?

 

[MD610]

Are you adding all these fields to the Row area of the pivot? Do you have any measures in the values? If you don't you need some type of measure (can be a simple count of one of the row fields) to "help" filter the data in the Rows. You can hide the measure in excel by simply hiding the column. When you have relationships between multiple tables and are mixing and matching pivot Rows and slicers from those tables, you need some type of measure to get the slicers to filter the Rows properly.

 

[pete234]

Yeah I was putting some data in row fields which pp didn't like. The only central join they have is on Account ID.
That is just to find the rep and some minor calculations from those tables. This works fine actually replacing lets see, 140k formula.
But I was trying to work the small tables backwards which isn't feasbile.
I proved this by importing into Access with a simple one to many join and of course, there are more ids in the little table so it won't "line" up anyway to the big table.