Forum Discussion

ADP007's avatar
ADP007
Helper IV
8 years ago
Solved

rank

Hello,   Havong difficulty with a rank formula  Ranking-1 = RANKX(ALL('Media Agency'[Media agency Group]);CALCULATE([FY-1 Price]))   Result is correct when used in a matrix, when used in a Card...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ADP007,

     

    It should be 11. The YYYYMM won't change the rank number as you need. You can't use the old measure as standards. 

    rank2

     

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ADP007,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi ADP007,

 

That's because you introduced new contexts. Please try the formula below.

Rank 2 =
IF (
    CALCULATE ( SUM ( Sheet1[Invest] ) ) = BLANK (),
    999,
    RANKX (
        ALL ( Sheet1[Agency] ),
        CALCULATE ( SUM ( Sheet1[Invest] ), ALLEXCEPT ( Sheet1, Sheet1[Agency] ) )
    )
)

rank

 

Best Regards,

Dale

ADP007's avatar
ADP007
Helper IV
8 years ago

Hi Dale,

 

Thanks for your reply. It does work for the example with OMNICOMMEDIA but then playing around with the slicer it doesn't always work.

 

What I am trying to achieve in another report is to point out the ranking of one specific company GROUPM (company I work for) whatever filter is used (CINEMA , INTERNET, OOH,...)

 

Can't share this report as it is with a direct connexion to an olap cube.

 

Thanks

David

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ADP007,

     

    It should be 11. The YYYYMM won't change the rank number as you need. You can't use the old measure as standards. 

    rank2

     

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ADP007,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale