Dear All

I have a Power Pivot table which consists of three external data sets.

My table consists of 4 Tables Dept Name, Score1. Score2, Score3. The three Scoretables are linked to Dept Name.

I have listed a Pivot table which has the Dept Name from Dept Name table and the scores from Score tables 1-3 in three columns.

I also have created 3 measures which calculated the Ranking by using the RankX funcion to show the ranking of each scores (1,2 &£) against departments.
I also created another measure which finds the average of the ranking by using the follwoing formula
Average Rank = Rank Score1 + Rank Score 2 + Rank Scores 3 /3

All these measures are created in Score 1 Table.

What I am trying to do now is to rank the average Rank by using the following measure formuls
Total Rank= =RANK.EQ('Score1'[Average Rank],'Score1'[Average Rank])

but get the follwiong error
Column 'Average Rank' in table Score1' cannot be found or may not be used in this expression.

I presume this is because Average Rank is a measure and not calculated field. Is there any way roud this asI need to show the total ranking based on the individula score Rankings.

when I use Rankx=(all('Dept Name'[Dept Number]','score1'[average Rank')) I get large numbers nstead of ranking.

Any help or advise is appreciate it
Thanks in advance