From Power Pivot to Pivot table

[lezawang]

Hi
I have 2 tables in 2 different sheets and I want to do Vlookup using Power Pivot. So I want to know the Salary of each employee. I managed to upload both tables to PowerPivot then I created a link (one to many) then I upload them to Excel Pivot table. I pivot table when I tried to create a table which show Employee Name and Salary, excel added all employees salary and assign the resul to each employee. I tried different way still not getting the right answer. I would appreciate if you can help. The file is shared below but also pasting my tables here. Thank you very much

Book1 (1).xlsx

drive.google.com

Book1 (1).xlsx
	A	B	C	D	E
1	Name	address	ph#	city	car
2	mike	1 main street	1	toronto	no
3	eric	2 main street	2	ottawa	yes
4	erica	3 main street	3	montreal	yes
5	dan	4 main street	4	toronto	no
6	danny	5 main street	5	toronto	yes
7	sammy	6 main street	6	ottawa	yes
8	maddy	7 main street	7	montreal	no
9	sean	8 main street	8	toronto	yes
10	shane	9 main street	9	toronto	yes
HR

Book1 (1).xlsx
	A	B	C
1	name	salary	phone1
2	john	100	1
3	mary	200	2
4	alex	300	3
5	sam	400	4
6	ed	500	5
7	sue	600	6
8	jack	700	7
9	lynn	800	8
10	david	900	9
Salary

Book1 (1).xlsx
	B	C
3	Row Labels	Sum of salary
4	dan	4500
5	danny	4500
6	eric	4500
7	erica	4500
8	maddy	4500
9	mike	4500
10	sammy	4500
11	sean	4500
12	shane	4500
13	Grand Total	4500
pivot

 

[severynm]

When you create your pivot table, make sure to choose "Use this workbook's Data model". You should then see a list of all the tables in your workbooks. If you drag the fields you want, it will probably ask you to create a relationship between the tables, or you can do it manually via Data → Data Tools → Manage Relationships. Relate the name columns in both tables, and you should end up with exactly your third table.