Using Rank and ignoring selections

[#1]

I'd like to use Rank but ignore my selections.

I am using the following expression:

AVG(Total {<CONSULTANT_ID = {"=Rank(SUM(TS_CON_HR_FEES_SUM_L123))<=5"}>}[TS_CON_OT_JOBS_CNT_L123])

which works perfectly until I make selections and then the rank works on the selected consultants - I want it to continue to use the whole data set. I know {1} denotes the whole data set ignoring selections but I don't know where to put it in the expression above.

As an extension I would like to perhaps retain some dimensions that have been used. For example there is an OFFICE dimension and if an OFFICE has been selected then I'd like to use that selection before I rank my top 25.

Please help me - I have been battling all day with this!

[#2]

The Average Jobs for the Top 5 consultants should be 43.4

When you select an OFFICE or a REGION on the left the Average Jobs for the Top 5 consultants should stay the same; instead they change to a different number.

Note that if you select consultants from the table (clicking on CONSULTANT_ID) the Average Jobs for the Top 5 consultants remains what it should be (43.4). Different behaviour!!

[swuehl · Accepted solution]

try

=AVG(total {1<CONSULTANT_ID = {"=Rank( SUM ({<CONSULTANT_ID=, A_CON_OT_REGION=, A_CON_OT_OFFICE= >}TS_CON_HR_FEES_SUM_L123))<=5"} >} [TS_CON_OT_JOBS_CNT_L123])