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,

 

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,

     

    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,

     

    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,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale