Ranking - problem when filtering only one value

[Anonymous]

Hi All,

I have a problem with RANKX function. I need to prepare a report in which a user will be able to select one category value, and will see rank in terms of a measure of that value.

I've prepared a DAX function, but it doesn't work in each case, sometimes the rank of selected category is different than the actual (usually by 1 place).

My data table looks like this (40k rows):

I want to rank values from the 'Category A' column by the sum of values in the "Measure 1' column

Dax function: 

Rank Category A by Measure 1 = 
IF(
    HASONEVALUE(data[Category A]),
    RANKX(
        ALL(data[Category A]),
        calculate(SUM(data[ Measure 1 ]))
    )
)

It works fine for most cases, but sometimes the rank number is wrong, as in the below screenshot. If we look at the bigger table with all categories then selected '6_Cat' has rank number 6 (and it's correct), but when we filter out only 6_Cat (table on the right) then its place is 5 (what is wrong).

I've noticed that when there is less data then the wrong rank appears less often, but still, it is.

Would be very grateful for the help, thanks in advance!

[amontoulieu]

Hello @Anonymous , I have the same issue, et no way to find out why?
Any progress on this problem?

Thx

[Anonymous]

Hi @amontoulieu , unfortunately, I still do not have solution

[amontoulieu]

Hi @Anonymous , try to use HASONEFILTER, it worked for me.

[Anonymous]

Hey @amontoulieu , thanks for the info but still not working.

It's fine when there is no filter on dates, but when I change the data range, then the result is different. On the screen below Category 10 should have rank 8, but it shows 3.

[Anonymous]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

If the above one can't help you get the desired result, please provide some sample data in your table 'data'  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hello @Anonymous ,did you find a moment to look into this? any idea what might be wrong?

Please find below the screen on which I've marked the wrong rank

[Anonymous]

Hey @Anonymous , thank you for your help.

Unfortunately, your solution is the same as mine, and there are rare cases in which filtered rank shows bad values. For example 27_Cat and 6_Cat are wrong (in my file)

Please find a link to my pbix file:

https://www.dropbox.com/s/fhbpj7pgragr2g6/Ranking%20test.pbix?dl=0