RankX - how to solve when I do have negative numbers and I want a continuous sequence?

[fcarvalho75]

Hi,

I did create a ranking, however I am facing an issue, when I have negative amounts, the ranking goes to 1384 up.

I need to generate a sequential ranking, is this possible?

I would like to be as: 102, 103, 104, etc...

Is this possible?

Thank you so much.

[pgrandits]

I have experienced the same gap in ranking negative numbers.  Can anyone explain how exactly the formula leaves what appears to be a very precise gap between the positive numbers and the negative numbers?

[GilbertQ]

hi @fcarvalho75

Can you confirm how your RANKX measure looks like. I am guessing that there is something missing which is causing the rank to not increment correctly?

[fcarvalho75]

Hi, I could find the solution by my own. I added the extra column as a filter (month) and now is working. THanks a lot

[fcarvalho75]

Guys,

I have a new question. I could solve the issue with negative number, but I am struggling to make a ranking with filters. On this example below, my ranking is based on my Qty, the second criteria to identifiy who comes firts is the hiring date. Is this possible to make? My formula in powerbi is: 

(#) = rankx(ALLSELECTED(dm_consultant[(Consultant Name)]);CALCULATE([(Total Qty)];(ALLSELECTED(dm_consultant[hiringDate]))))

I'm quite sure I have to do some filter or take from other information, like sales revenue, instead of hiring date.

Any help I really appreciate it.

[Sean]

@fcarvalho75

The way I read your question you want the Hiring Date to break the ties

I've seen this pattern from @OwenAuger and I use it in my reports

Give this a try... it works with my data sets

(#) =
RANKX (
    ALLSELECTED ( dm_consultant[(Consultant Name)] );
    RANKX (
        ALLSELECTED ( dm_consultant[(Consultant Name)] );
        CALCULATE ( [(Total Qty)] );
        ;
        ASC
    )
        + DIVIDE (
            RANKX (
                ALLSELECTED ( dm_consultant[(Consultant Name)] );
                CALCULATE ( MAX ( dm_consultant[(hiring)] ) );
                ;
                DESC
            );
            ( COUNTROWS ( ALLSELECTED ( dm_consultant ) ) + 1 )
        )
)

Hope this helps!

[fcarvalho75]

Hi Sean,

I tried to use this formula, but gives me an error. I understood I can have the same date for different consultants. So I created a new field unique to use instead of hiring date, but it didn't work either.

[Sean]

Can you post the Measure formula

[fcarvalho75]

Hi Sean, sorry, I didn't understand you. What measure you wanna see?

[Sean]

Aren't you creating a Measure? Isnt't (#) a Measure?

[fcarvalho75]

OK... I use your formula

(#) =
RANKX (
    ALLSELECTED ( dm_consultant[(Consultant Name)] );
    RANKX (
        ALLSELECTED ( dm_consultant[(Consultant Name)] );
        CALCULATE ( [(Total Qty)] );
        ;
        ASC
    )
        + DIVIDE (
            RANKX (
                ALLSELECTED ( dm_consultant[(Consultant Name)] );
                CALCULATE ( MAX ( dm_consultant[(hiring)] ) );
                ;
                DESC
            );
            COUNTROWS ( ALLSELECTED ( dm_consultant ) + 1 )
        )
)

[Sean · Accepted solution]

@fcarvalho75

Okay try it now...

(#) =
RANKX (
    ALLSELECTED ( dm_consultant[(Consultant Name)] );
    RANKX (
        ALLSELECTED ( dm_consultant[(Consultant Name)] );
        CALCULATE ( [(Total Qty)] );
        ;
        ASC
    )
        + DIVIDE (
            RANKX (
                ALLSELECTED ( dm_consultant[(Consultant Name)] );
                CALCULATE ( MAX ( dm_consultant[(hiring)] ) );
                ;
                DESC
            );
            ( COUNTROWS ( ALLSELECTED ( dm_consultant ) ) + 1 )
        )
)

[fcarvalho75]

Thank you so much Sean! This is exactly what I looking for. You rock!

[v-qiuyu-msft]

Hi @fcarvalho75,

Based on my test, negative numbers can also be ranked use RANKX() function. See:

In your scenario, the issue should be related to your table columns and DAX. If possible please share the sample report with us to analyze this issue.

Best Regards,
Qiuyun Yu