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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.