Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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)] ) )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago
      Hi

      Try with

      ALL('WMGBU BEx Query'[Customer], 'WMGBU BEx Query'[profit center])
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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!