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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

RANKX ISSUES

I have been trying to iron out this issue for a week now.  I saw in tutorials that if you use ALL in your RANKX formula that the Rank result would not be "Ones".  I have done so in the below example and yet have "Ones" as the Rank result.  What other things could be causing this?

 

 

Total Pounds WMGBU.jpg

1 ACCEPTED SOLUTION

@Anonymous

 

Or with this

 

Ranking =
RANKX (
    SUMMARIZE (
        ALLSELECTED ( 'WMGBU BEx Query' ),
        'WMGBU BEx Query'[ENT BASE ITEM],
        'WMGBU BEx Query'[profit center]
    ),
    CALCULATE ( SUM ( 'WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)] ) )
        / CALCULATE ( SUM ( 'WMGBU BEx Query'[REVENUE (PROJECTED)] ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
hthota
Resolver III
Resolver III

The below link would help you to resolve the issue.

Link: http://community.powerbi.com/t5/Desktop/How-to-do-Ranking/td-p/342952

 

Anonymous
Not applicable

Alas this tells me everything that I have completed already.  I have the rank appearing correctly now...my issue is if I want to add a profit center to the table (not a filter) the rank changes to by profit center as well.  I need the rank regardless of the profit center which this thread does not address.

@Anonymous

 

Try with

 

Ranking =
RANKX (
    ALL ( 'WMGBU BEx Query'[ENT BASE ITEM], 'WMGBU BEx Query'[profit center] ),
    CALCULATE ( SUM ( 'WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)] ) )
        / CALCULATE ( SUM ( 'WMGBU BEx Query'[REVENUE (PROJECTED)] ) )
)

Regards
Zubair

Please try my custom visuals

@Anonymous

 

Or with this

 

Ranking =
RANKX (
    SUMMARIZE (
        ALLSELECTED ( 'WMGBU BEx Query' ),
        'WMGBU BEx Query'[ENT BASE ITEM],
        'WMGBU BEx Query'[profit center]
    ),
    CALCULATE ( SUM ( 'WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)] ) )
        / CALCULATE ( SUM ( 'WMGBU BEx Query'[REVENUE (PROJECTED)] ) )
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

One last question...is there a way to add to this formula that will restrict the results to only the top 10?

 

Thx

Hi
Sorry for late reply. I am out of town actually
You can put the measure in visual filter and select top 10

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

I actually tried that with the Rank Measure and what would happen was from to month the viewable rows would change...10 one month, 8 another and sometimes only 3?  That stumped me!

Anonymous
Not applicable

This one worked perfectly...thx so much for your time and efforts!

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try replacing ALL('WMGBU BEx Query') with ALLSELECTED('WMGBU BEx Query'[Customer])

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thanks that actually worked!!!  So there is a part two to this question now.  After I made your change I went to add in a column with the Profit Center name and then the rank changed to rank the results also by profit center...so there are multiple ones, twos, threes, etc.  Is there a change that can be made in the formula so that it does not factor in the profit center on how it is ranking items?

 

Hi

Try with

ALL('WMGBU BEx Query'[Customer], 'WMGBU BEx Query'[profit center])

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

I tried to insert that into my formula and it errored out.  Here is my actual formula....where should this be inserted ('WMGBU BEx Query'[profit center]) ?

 

Ranking = RANKX(ALLSELECTED('WMGBU BEx Query'[ENT BASE ITEM]),CALCULATE(SUM('WMGBU BEx Query'[DIRECT MARGIN (PROJECTED)]))/CALCULATE(sum('WMGBU BEx Query'[REVENUE (PROJECTED)])))

 

Thx for your assistance!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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