Hi All
I have a quick help with the Calculate function and also want to know what I am trying to achieve is possible by using Calculate function.

I have a Power Pivot table which consists of the following table:
Sm List:
Contains Name : Name of Managers and Site Code : department Numbers. There is no unique field in this table as two mangers can have the same department numbers

Trade Figures
This data comes from Analysis server and has Site Code: Department Numbers and Sales: Trade figures for each department. Again there will be repeated values for departments.

Dept NO
Unique list of Department Numbers (col name=Site Code) and the divisions (col name=Divisions) they belong to.

I have a relation set up between Dept No with Sm List and Trade based on unique department number in Dept No. Table.

I need to create a report where from SM Table I can list the SM list and Department numbers and have a measure which will look at the department number and from Trade Figures table pulls the appropriate Sales value for that department. So that in my Pivot table I have name, Department and Trade figures. I can do this in normal excel by using VLookup
Unfortunately I can not create a relationship between Trade Figures and SM List as there is no unique field between those two.

The data is supplied by external source and I have no control on the filed within the tables.

Is there anyway that I can use Calculate function or any other appropriate function to be able to achieve what I need to achieve, if so an example will be really appreciate it