Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BlackBull256
Regular Visitor

RankX with others does not show the correct value.

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

 

BlackBull256_1-1623403875205.png

 

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

 

 
1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

rank by company.gif

 

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.

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

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:

rank by company.gif

 

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.