RANKX(ALLSELECT()) not working as expected

[scorbin-j]

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. 

[Anonymous · Accepted solution]

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

[rajendraongole1]

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]

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

[rajendraongole1]

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!!

[scorbin-j]

It says the that it can't be found or used in the measure

[rajendraongole1]

Hi @scorbin-j - Please cross check the table name you are using and in your table first calculate RankDelta column as below:

Take a calculated column use the below dax:

RankDelta = Rnak[Budget Hours]-Rnak[Actual Hours]

after that Create your measure:

sum Rank Delta = sum(your TableName [RankDelta])

it works

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

[scorbin-j]

The Rank Delta is a measure, not a column in a table. That table with the Rank Delta is just the table visual in Power BI.