Forum Discussion

kobkabnaja's avatar
kobkabnaja
Helper II
1 year ago
Solved

Question on RANKX formula and selected value

Hi community,

 

I have a question about RANKX formula. I am using it to rank states based on net population inflow. The formula I used is shown below, and the results appear to be correct (see attached).

FormulaResult

 

 

 

 

 

 

 

 

 

However, when I filtered for a single state, the ranking reset to 1 for evey state instead of retaining the original rankings from the previous table. I am not sure what went wrong and would appreciate any suggestions on how to resolve this.

 

 

 

 

 

 

 

 

 

 

Thank you,

K.

Need Help
  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    1 year ago

    Anonymous is correct. These 2 have "Dont Summarize", and it is parsing out the states further.


    If you remove them, or change them to sum or a different aggregation, Rank would work again using:

    RANKX(
        ALL(migrationt[Name]),
        CALCULATE(SUM(migration[netflow_tot]))
    )

     

19 Replies

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    1 year ago

    Try this..

    *edited to add Calculate in orange

    RANKX(
        ALL(migrationt[Name]),
        CALCULATE(SUM(migration[netflow_tot]))
    )
    • kobkabnaja's avatar
      kobkabnaja
      Helper II
      1 year ago

      Hi Tutu_in_YYC ,

      Thanks for your prompt response. I did try that formula. But it did solve the problem. When I selected only one state, it still showed rank 1.

       

      I would appreciate any further suggestions you may have.

       

      Best,

      K.

      • Tutu_in_YYC's avatar
        Tutu_in_YYC
        Super User
        1 year ago

        interesting... we tried on my end, and it works... see below:


        after selection

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi kobkabnaja ,

    Thanks for Tutu_in_YYC's reply!
    And kobkabnaja , Please change ALLSELECTED('migration') in your original DAX to ALL('migration'), and do not add any fields after 'migration'.
    That is:

    RANK_NETFLOWTOT = 
RANKX(
    ALL(migration),
    CALCULATE(SUM(migration[NETFLOW_TOT])),
    ,
    DESC,
    Dense
)

    Output:

    Because you have placed other fields in your visual object and you also have two slicers, using ALLSELECTED or ALL for a certain field will still cause the measure to be affected by the filtering of other fields and slicers in the visual object, resulting in changes in the results.

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • kobkabnaja's avatar
      kobkabnaja
      Helper II
      1 year ago

      Hi Anonymous, thannks for chiming in. I have tried that and it still does not work out. It ranked all the states regardless of the year. Please see the attached for your reference.

       

      I plan to share the .pbix file, but I could not find the place to attache the file here.

       

      Do you think it is because I cannot rank all the state by year properly? Ideally, if I can rank all the states by year first, I can pull the correct rank using the state and year. Is that the case?

       

      Thank you,

      K.

      • Tutu_in_YYC's avatar
        Tutu_in_YYC
        Super User
        1 year ago

        you can use 3rd party sharing, like dropbox to share your file. 

        It should work no problem with the year column too...This is mind boggling 😄