Forum Discussion

scorbin-j's avatar
scorbin-j
Icon for Helper I rankHelper I
2 years ago
Solved

RANKX(ALLSELECT()) not working as expected

I have a table called All Hours that is an appended table from an Actual Hours table and Budget Hours table. It contains an ID Number, Actual hours, Budget hours, and Delta hours. Delta hours is a column created from Budget hours - Actual hours. Example of the table is this: 

 

I then created a measure of Sum Delta = SUM(Delta). I use this measure to create the Rank Delta measure which is Rank Delta = RANKX(ALLSELECTED('All Hours'), 'Measure'[Sum Delta],,ASC,Dense). That gives me these results:

 

 

I was expecting to see results for the ranks being 1-4 here. I am not sure why I am getting these results or where I am going wrong. The other thing I would mention is I am doing a similar thing in the same report, but using a different source table that is not an appeneded table and I get the results I expected to get. 

Need Help

7 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi scorbin-j - Create a new measure with RankX function as below

     

    I have created two measures:

    Measure 1: for total on delta

    deltaram = SUM(Rnak[Delta])

     
    Measure 2: to find the rank in sequence
    Rank Delta_2 = RANKX(
        ALLSELECTED('Rnak'[ID Number]),
        [deltaram],
        ,
        ASC,
        DENSE
    )
     
     
    I have taken a table chart, output for your reference.
     

     

     

    Please use the same and let me know.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     
    • scorbin-j's avatar
      scorbin-j
      Icon for Helper I rankHelper I
      2 years ago

      Power BI doesn't seem to be letting me sum the Rank Delta measure. 

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        2 years ago

        Hi scorbin-j - what is the error you can share it here,

         

        please create a measure for delta, use that delta measure in Rank measure .

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!
        Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, scorbin-j 

    Thanks for rajendraongole1 help. After reading your description carefully, the Delta column is a Measure, you can try to use the following Measure.




    Rank Delta = 
RANKX(ALLSELECTED('All Hours'), 'All Hours'[Delta],,ASC,Dense)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum