RANKX ISSUES

[Anonymous]

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?

[hthota]

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]

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.

[Zubair_Muhammad]

@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)] ) )
)

[Zubair_Muhammad · 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)] ) )
)

[Anonymous]

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

Thx

[Zubair_Muhammad]

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

[Anonymous]

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]

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

[Zubair_Muhammad]

@Anonymous

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

[Anonymous]

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?

[Zubair_Muhammad]

Hi

Try with

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

[Anonymous]

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!