RANKX exclude from rank, but include in table

Hi M_Aird

Here's an idea that should work without changing your existing tables:

(sample pbix uploaded here)

1. Change your rank measure to this (updated slightly to rank over the State table as well as return blank result for Australia):
   

   State ranking (asc) = 
   IF (
       SELECTEDVALUE ( State[State and territory] ) <> "Australia",
       RANKX (
           CALCULATETABLE (
               ALLSELECTED ( State ),
               State[State and territory] <> "Australia"
           ),
           CALCULATE ( SUM ( Current_month_labourforce[Current month value] ) ),
           ,
           ASC
       )
   )

2. Add a "dummy" measure to rank the final table, which always gives Australia the highest possible rank:
   

   R = 
   IF (
       SELECTEDVALUE ( State[State and territory] ) = "Australia",
       COUNTROWS ( ALLSELECTED ( State ) ),
       [State ranking (asc)]
   )

3. Create a table containing R, State ranking (asc), State and territory and Current month value
4. Sort by R
5. Reduce the width of the R column heading until it is invisible.
6. Result looks like this:

    

Well that's one method anyway. You could also add a special flag to the State table to flag Australia as being excluded from the ranking.

Cheers,

Owen :)