Table Realationship inPower pIvot help needed

[rv02s]

Hi All

I have a query and am not sure if it is at all possible to do.

I have a large power pivot file and need to create a Pivot report but am stuck in figuring out how to set up relationship between my tables.

In simple terms below Is what I need.

I have three table called A, B and C

A contains
Dept No Division
A CC
B DD
C DD
D EE
in this table the dept No are unique.

B Contains
Manager Name Dept
XXX A
YYY B
ZZZ A
TTT B
HHH C
XXX C
HHH B
In this table two managers can be responsible for one department

C Contains
Dept Scores
A 10
A 20
B 22
B 21
B 12
C 16
C 20
D 20

This table (which is supplied by different source) has department numbers and scores for each dept . One department can have different scores.

What I need to somehow create relations ship between my tables so that the department codes come from Table A and then it takes the names of the managers for those departments from table B and the Pivot them and add the relevant score for each department from table C. So the end result I will have

Managers Name Department Codes Scores
XXX A 46 (10+16+20)
C
HHH C 22+21+12+16+20
B

and so on.

I can get the dept names from Table A and match them with the scores from Table C but not sure how to link Table C to table B to get the names.

Is this at all possible or do I have t rethink the whole design. Unfortunately the data is supplied to me from various sources which I do not have control over. These tables in realty are very large and Power Pivot seems the answer if I can somehow match the names to departments codes in Table C

I hope I am clear in what I am trying to achieve

Thanks in advamce

 

[Kazlik]

I think if you create a relationship between Table A and B on Dept and then Table A and C on Dept. Then create a measure on Table B ManagerScore:=CALCULATE(SUM(Table C[Scores]),FILTER(Table B,Table B[Manager Name]=B[Manager Name])) that might work.

 

[rv02s]

Hi

Thaks for your reply.

I tried this and it doesnt seem to work. I may be doing something wrong.

Just to calrify, I need my Pivot table to have tne Names from Table B, Dept Code from Table A and Scores from Table C.

Any further help is appreciate it

 

[scottsen]

The only real relationships you can create are based on the "Lookup Table" for departments. That dude can be used to filter both of the other tables.

I *think* you should be able to then use Deparment (from the lookup table), plus Manager (from the manager table) and SUM([Scores]) from ... though, maybe a bit trickier if you want to divide the score by # of managers or something.