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

ADP007's avatar
ADP007
Helper IV
8 years ago

Hi all,

 

I have a reproduced the error in a report I can share

When I unselect a date the ranking is wrong in the Card

Any help always welcome

 

Thanks

D.

 

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

Hi ADP007,

 

You can upload the file to a cloud drive like OneDrive, GoogleDrive then share the download link here. Please mask the sensitive data first.

 

Best Regards,

Dale

  • ADP007's avatar
    ADP007
    Helper IV
    8 years ago

     

    Hi Dale,

     

    Many thanks in advance. I hope the link works.

    The error occurs when the selected agency doesn't have any investments for a date.

    In the example below OMNICOMEDIAGROUP doesn't have any data before 201807 and when I select that date the ranking in the matrix is correct but the ranking in the card is not correct.

     

    PowerBi

     

    Regards

    David

     

     

    • 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