RANKX Help

[Boycie92]

Hi

I was wondering if someone could help me.

I want to create a measure that will work based on the following conditions:

I want it to show the top 4 Highest Performers and filter out everyone else. However I also want it to include a Unique Person in the Ranking. This person no matter what their score is will always be ranked as Number 4 unless they are ranked higher.

For Example

Department A:

Name           Rank    Score

Joe               1            9

Jen               2            5

Ben              3            4

Unique ID    4            2

OR

Department A:

Name           Rank    Score

Joe               1           10

Unique ID    2            7

Ben              3            4

Jen               4            1

I have the following columns:

Department 

Name

Score

Thanks in advance for the help.

[v-sihou-msft]

@Boycie92

According to your description, you actually want to filter out top 3 performer and 1 specific Person, RANK all 4 people. Right?

In this scenario, you can use TOPN() function to create a calculated table with the 3 best performers. Then use "Union Query" in Query Editor to combine a dataset with that specific person record only. Now you can create a calculated column to RANK all 4 people.

Regards,

[Boycie92]

Hi @v-sihou-msft,

Thanks for your response.

I already have the specific person within the data set. Do I still need to follow the process you recommended or can I just use the TOPN() function?

Thanks,                                                                                                                                                                                      

[v-sihou-msft · Accepted solution]

@Boycie92

Since you always want to include that specific person in "the four", you need to exclude that person in the dataset and rank the top 3 first, then UNION that person together. 

Regards,