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 Dale,

 

 [FY-1 Price] is a calculated measure in my tabular model 

FY-1 Price:=CALCULATE
 (
    [TotalPrice];
    FILTER(Calendar;Calendar[MDB_FYIndex] =  -1 )
)

I have a DimDate table where some index give running year, last year etc....

 

How can I share the file with you?

Regards

David

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