I was wondering whether someone can give me an advise on how to properly set up a relation in my Power Pivot to be able to obtain the correct result.
I have attached the diagram on how my relationship is currently set up for your reference.
As you can see from the attached diagram I have relationship between Am List, Concession Gues...., SM List, Used OP Report and Dept No by site code. Site Codes in Dept No are unique.
What I need to do is to design a Pivot Table report whereby Site Codes come from Dept Number, the the Names for those Site codes to come from Am and SM list and the scores for those names and Dept Numbers to come from Used Opp and Cocens... files.
When I just choose the Dept No and the scores this works perfectly but when I get the Site Code from Dept No and then want the Am and Sm names for those departments to come from Am and SM list then it does not work as it gives me the same scores for all departments.
Any help is appreciate it as to how I can set this up properly or do I need another sheet.
Thaks in Advance
Bookmarks