Rank top 10 highest salary without employee name ?

[M4AMA]

Hello everyone,

I have a REMUNERATION table as below :

I wish to establish a top 10 annual salary, over 3 years, but anonymous. The employee's name must not appear in the table because the report will be made public.

The final result should look like this, in a matrix:

I started by creating a first measure to filter on the salary heading:

SalarySum =

CALCULATE( [Amount] ;

    FILTER(REMUNERATION ;

        REMUNERATION[Heading]="Salary"

        )

    )

Then, looking on the forum, I could create the following measure:

SalaryRank =

RANKX( ALLSELECTED( REMUNERATION[Name] ) ; [SalarySum] )

But I can not use both [SalaryRank], REMUNERATION[Year] et [SalarySum]…

Do you have an idea ? Any help is welcome !!

[Anonymous]

We blind data by making an ID using a formula that incorporates date and math on the employee ID. That can be inserted as a Column. 

Here is an example of a really messed up mask. I put a snippet of the resilts below to show you that the real ID is completely shufflled. This will change every update due to using RAND(). Just use this and replace my fields with your tables[Field}s. If Employee ID is very long (large number) divide by 10,100 etc.

AAA FakeID = ROUND(Customer[CustId]*(RAND()*100)+333,0)+(RAND()*10)*(ROUND(TODAY(),-2))/Count(Dates_INV[day])

Enjoy!

[M4AMA]

Hye Moscuba,

Your ID anonymization solution is interesting, I will help!

But in my situation, it will not be enough. Indeed, the reference to an employee (or its ID, even hidden) can not be used in the matrix.

As the ranking is over 3 years, the employee who ranks # 1 in 2015 is not necessarily the same as in 2016 or 2017. And it's the same problem for all ranks : the ranking moves every years.

So I have to focus only on the annual amount, no matter the employee behind it.

Is there a way to do this?

Thank you in advance !

[tex628]

You will need a anonym ID like previously proposed. If you create it in the table as a calculated column you can use it without displaying the employee ID 🙂 

[M4AMA]

Thank you very much for your help, but I can not get the desired result, I think I'm expressing myself badly.
Looking again, I could find a solution, but that does not satisfy me completely.
There she is :

I created a derived table 'ANNUAL_SALARY' which makes the sum of the annual salary:

ANNUAL_SALARY =
SUMMARIZE(
 FILTER ( REMUNERATION ; REMUNERATION[Heading] = "Salary" )
   ;REMUNERATION[IDemployee]
   ;REMUNERATION[Year]
   ;"SalarySum" ; SUM( REMUNERATION[Amount] )
   ;"KeyIDemployeeDay" ; MAX( REMUNERATION[KeyIDemployeeDay] )
   ;"KeyIDemployeeMonth" ; MAX( REMUNERATION[KeyIDemployeeMonth] )
 )

Then I created a calculated column [SalaryRank]:

SalaryRank =
RANKX( FILTER( ALL( 'ANNUAL_SALARY' )
  ; 'ANNUAL_SALARY'[Year] = EARLIER( 'ANNUAL_SALARY'[Year] )
   )
   ;'ANNUAL_SALARY'[SalarySum]
   ;
   ; DESC
 )

Result :

The problem is that I have to create a derived table, which I did not want to do, so as not to overload the list of available tables.

Is there a solution to achieve the same result without a derived table? Only with measurements?

Thank you all!