Register
Log In
Unanswered
Home
Power Query
October 14, 2015
Offline
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.
Power Query
October 14, 2015
Offline
I've attached a small, sanitized file that exhibits the problem I'm facing. Thanks for your help!
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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!
July 16, 2010
Offline
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
Power Query
October 14, 2015
Offline
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.
July 16, 2010
Offline
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
Most Users Ever Online: 57
Currently Online:
8 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
