RankX with others does not show the correct value.

[BlackBull256]

Hey all, 

I have a dataset which has data from multiple companies, departments with the corresponding revenue. I have a measure which ranks companies based on revenue within each department. So it not a total ranking, but a ranking on department level. 

Because I want to create a Top N filter with an others category, I have created a seperate table with all the distict Company names, and the value "Others". In my ranking measure (see code below) I tell the measure to allocate the value -1 when the Company name equals "Others", else allocate the Ranking. However, as you can see in the table, the "Others" values are still included in the ranking, whilst I actually want to see the -1 value. 

Does anyone know what I am doing wrong? Your help would be greatly appreciated. You can access the file her: https://www.dropbox.com/s/yhcfux2lzrnmm4f/RankingTestFile.pbix?dl=0

Ranking = 
VAR Ranking = 
    RANKX(ALLSELECTED(CompanyNames[Company]),
        CALCULATE(
            SUM(Sheet1[Revenue]),
            NOT ISBLANK(Sheet1[Company]),
            ALLSELECTED(Sheet1[Company])
        ))

VAR isOthersSelected = SELECTEDVALUE(CompanyNames[Company])="Others"
VAR Result = if(isOthersSelected,-1,Ranking)
RETURN Result

[v-eqin-msft · Accepted solution]

Hi @BlackBull256 ,

Please follow these steps:

1. Create a Company table:

Company = UNION( VALUES(Sheet1[Company]) ,{"Others"})

2. Add a measure to calculate the sum of Revenue:

Sum Revenue = CALCULATE(SUM('Sheet1'[Revenue]),FILTER('Sheet1','Sheet1'[Company]=MAX('Company'[Company])))

3. Add the rank measure:

Rank = IF(MAX('Company'[Company])="Others",-1, RANKX(ALLSELECTED(Company),[Sum Revenue],,DESC,Dense))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BlackBull256]

@v-eqin-msft , thank you very much, this works perfectly!