topic RANKX(ALLSELECT()) not working as expected in DAX Commands and Tips

RANKX(ALLSELECT()) not working as expected

scorbin-j — Fri, 07 Jun 2024 16:53:41 GMT

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.

Re: RANKX(ALLSELECT()) not working as expected

rajendraongole1 — Fri, 07 Jun 2024 17:10:27 GMT

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

Re: RANKX(ALLSELECT()) not working as expected

scorbin-j — Fri, 07 Jun 2024 17:19:23 GMT

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

Re: RANKX(ALLSELECT()) not working as expected

rajendraongole1 — Fri, 07 Jun 2024 17:21:41 GMT

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

Re: RANKX(ALLSELECT()) not working as expected

scorbin-j — Fri, 07 Jun 2024 17:31:02 GMT

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

Re: RANKX(ALLSELECT()) not working as expected

rajendraongole1 — Fri, 07 Jun 2024 17:54:02 GMT

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

Re: RANKX(ALLSELECT()) not working as expected

scorbin-j — Fri, 07 Jun 2024 17:55:51 GMT

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.

Re: RANKX(ALLSELECT()) not working as expected

Anonymous — Tue, 18 Jun 2024 07:18:43 GMT

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