I can't provide the file, for reasons of confidentiality, but I'm hoping a verbal description will suffice.
I have created a Data Model from two separate files, with Job Number as the shared key. When I create a Pivot Table using the salesperson from 1 table and the sales values from another, the total of all sales appears next to each salesperson. I then tried other combinations, but in all cases, the value next to each Row Label is the same (a grand total).
I'm hoping I'm making a well-known mistake, which someone can identify from the result I've described. Thanks for your help.
I've attached a small, sanitized file that exhibits the problem I'm facing. Thanks for your help!
The attachment helps a lot Bob (I would have asked for it). Unfortunately I can't find what's wrong. Tested several things. It looks like you made the connexion in the right way, but I'm not sure (not an expert in that). When I add the Job number from the second table, there is something happening, but still not the right answer. I'll look further (it's an interesting case), but I hope somebody else here can give a clue. Good luck!
Hi Bob,
The direction of your relationship was the wrong way around. Table 2 should lookup Table 1. Delete the relationship and in the diagram view drag Job Number from Table 2 over to Table 1 so the arrow is pointing towards table 1.
It would help to name your tables:
Table 1: is your dimension table with info about the jobs. I'd call this 'Jobs'
Table 2: is your fact table with transaction details that you want to summarise. I'd call this 'Financials' or similar.
Mynda
Mynda - Thanks for the diagnosis; I now see my problem. However, trying to fix it raised two other points.
1. I wasn't familiar with "diagram view," but found that it's part of Power Pivot. I'm using Excel 3016 from Office 365, but that add-in isn't available when I go to File>Options>add-ins and check both COM add-ins (as instructed by Microsoft Help) and Disabled Add-ins. When I go to download the add-in, it says that it's for Excel 2010. Any suggestions?
2. Meanwhile, I tried to apply your fix using the dialog box. It worked perfectly on the tiny example file I sent you, but when I try to apply it to the real file, it tells me there are duplicate values in both of the Project # fields I'm trying to use as keys. I confirmed that this isn't the case both by using the Delete Duplicates feature and by using an explicit formula in a helper column. Any idea why it sees duplicates when I don't?
Thanks for your help
Bob A.
In that case you should be able to manage relationships via the PivotTable Analyze tab in the 'Calculations' group > Relationships. You dont' have the nice diagram view in this dialog box but you can change the Table and Related Lookup Table in there.
So, your 'Table' should be Table2 and your 'Lookup Table' should be Table1. Sounds like you may have already done that, but just in case and for the benefit of others.
As for your duplicates problem, I'd have to see the file. If you don't want to share it on the forum you can email it to me direct. See our Contact Us page for details.
Mynda
