Show rank (Rankx) for user within 1 month with the same value

[yakovlol]

Hello team,

I have an issue with ranking in my table and cannot solve the issue

So I have a table like the one below. 

Full name	Project name	Date	Hours	RANK How it calculated now	Desired RANK
User 1	DDDDDD	2/1/2023	32	2	3
User 1	AAAAAA	2/1/2023	100	1	1
User 1	BBBBBBB	2/1/2023	100	1	2

I have created a calculated column that calculates the rank within a month for each value. However, when the values are the same, the same rank number appears. I want the rank to be determined based on the project name in ascending order, if users have the same value within one month, like the 'Desired RANK' column shown in the example.

Ranks = 
VAR ThisMonth = 'My table'[Date_]
VAR user = 'My table'[Full name]
RETURN
    RANKX(
        FILTER(
            ALL('My table'),
           'My table'[Date_] = ThisMonth &&
           'My table'[Full name] = user
        ),
        [hours], ,
        DESC, 
        Dense
    )

Maybe there is a way to fix it.

Thank you

[Ahmedx · Accepted solution]

Share sample pbix file to help you.

[Ahmedx]

pls try this

Ranks = 
VAR ThisMonth = 'My table'[Date_]
VAR user = 'My table'[Full name]
RETURN
    RANKX(
        FILTER(
            ALL('My table'),
           'My table'[Date_] = ThisMonth &&
           'My table'[Full name] = user
        ),
        [hours]& [ Project name], ,
        DESC, 
        Dense
    )

 

[yakovlol]

Hello @Ahmedx thank you for your approach. But now it's ranking based on text but not the hours values.

It's not shown on the example, but if we add another User 2, and put for him different Hours values and will be ranked based on Text, so in alphabetical order  because of this part  in our formula (hours & project name)

Full name	Project name	Date	Hours	How its calculated now
User 2	BBBBBBB	2/1/2023	101	1
User 2	AAAAAA	2/1/2023	102	2

[Ahmedx]

you can add column index ( in power query)?

[yakovlol · Accepted solution]

Hello @Ahmedx 

I found solution. So yeah I added Index column in PQ, before that I've sorted Project_name ASC.

Than modified my formula and seems it works now, and for dublicated values rank is set correct

Ranks = 
VAR ThisMonth = 'My table'[Date_]
VAR user = 'My table'[Full name]
RETURN
    RANKX(
        FILTER(
            'My table',
            'My table'[Date_] = ThisMonth &&
            'My table'[Full name] = user
        ),
        'My table'[hours] - [Index] / POWER ( 10, 9 ),,
        DESC, 
        Dense
    )

[Ahmedx · Accepted solution]

Share sample pbix file to help you.

[Ahmedx]

and try this

Ranks = 
VAR ThisMonth = 'My table'[Date_]
VAR user = 'My table'[Full name]
RETURN
    RANKX(
        FILTER(
            ALL('My table'),
           'My table'[Date_] = ThisMonth &&
           'My table'[Full name] = user
        ),
        [hours],[Project name] ,
        DESC, 
        Dense
    )

[yakovlol]

Hello @Ahmedx thanks for your answer
but now I'm faced with such an error "Function 'RANKX' does not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values"

[Ahmedx]

pls try this

Ranks = 
VAR ThisMonth = 'My table'[Date_]
VAR user = 'My table'[Full name]
RETURN
    RANKX(
        FILTER(
            ALL('My table'),
           'My table'[Date_] = ThisMonth &&
           'My table'[Full name] = user
        ),
        [hours]+ (RANDBETWEEN(1 , 1E18)/1E18) , ,
        DESC, 
        Dense
    )

[yakovlol]

Thank you for the reply. I tried to use this, but now, this calculation misses Rank 1, and started from 2, but the same rank for the same values, unfortunately.

so after this calculation i have rank number as below

Full name	Project name	Date	Hours	How its calculated now
User 1	DDDDDD	2/1/2023	32	3
User 1	AAAAAA	2/1/2023	100	2
User 1	BBBBBBB	2/1/2023	100	2