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 atta...
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]))
    )

     

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 😄

    • Tutu_in_YYC's avatar
      Tutu_in_YYC
      Super User
      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]))
      )

       

      • kobkabnaja's avatar
        kobkabnaja
        Helper II
        1 year ago

        Wow, I definitely would not have caught this issue without the help from you guys. Thank you so much!

        I fixed the columns by changing them to summarize, but the ranks are now 1 and 2 in stead of 1 and 50. lol. Did I do something wrong here? Would you mind editing the file and share it back with me. I truly appreciate your help!

        Thank you,
        K.