RANKX returning duplicate values

[KAATA]

Hi Power BI Community,

The below measure is supposed to return ranking based on the Region and the Continent columns.

The purpose is to consider both scopes – but independently of each other.

However, the measure returns rank 2 for two of the Regions - see screenshot below.

If I use ROUND for the Total Sales Measure, it works fine.

But why do I need to use ROUND?

The values are already quite different (about 24,000 for Southeast and 16,000 for Canada).

Looking for some DAX experts to help me out understanding this.

Thanks in advance 🙂

Measure 1: 

Rank Sales (considering hierarchy) =

IF(

    ISINSCOPE (DimTerritory[Region]),

    RANKX(ALLEXCEPT(DimTerritory, DimTerritory[Continent]), [Total Sales]),

    IF(

        ISINSCOPE(DimTerritory[Continent]),

        RANKX(ALLSELECTED(DimTerritory[Continent]), [Total Sales])

        )

    )

[lbendlin · Accepted solution]

yeah, that doesn't look right. Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[KAATA · Accepted solution]

Thank you for the reply, @Ahmedx.

I solved the issue in the following way:

First, I changed the data type for the Unit price from decimal number to whole number (in Power Query Editor).
I use Unit price to calculate the Total Sales:

Total Sales =

SUMX(

    FactSales,

    FactSales[Order Quantity] * FactSales[Unit Price]

)

Next, I used the below formula to calculate the ranking:

Rank Sales (disregarding hierarchy) =

IF(

    ISINSCOPE(DimTerritory[Region]),

    RANKX(

        ALLSELECTED(DimTerritory),

        [Total Sales]

    ),

    IF(

       ISINSCOPE(DimTerritory[Continent]),

    RANKX(

        ALLSELECTED(DimTerritory[Continent]),

        [Total Sales]

    )

    )

)

As a result, I obtain the correct result:

The only confusion left is why I had to change the data type from decimal to whole number for this to work.
But for now the issue is solved.

[lbendlin · Accepted solution]

yeah, that doesn't look right. Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[KAATA]

Hi @lbendlin,
I solved the issue in the following way:

First, I changed the data type for the Unit price from decimal number to whole number (in Power Query Editor).
I use Unit price to calculate the Total Sales:

Total Sales =

SUMX(

    FactSales,

    FactSales[Order Quantity] * FactSales[Unit Price]

)

Next, I used the below formula to calculate the ranking:

Rank Sales (disregarding hierarchy) =

IF(

    ISINSCOPE(DimTerritory[Region]),

    RANKX(

        ALLSELECTED(DimTerritory),

        [Total Sales]

    ),

    IF(

       ISINSCOPE(DimTerritory[Continent]),

    RANKX(

        ALLSELECTED(DimTerritory[Continent]),

        [Total Sales]

    )

    )

)

As a result, I obtain the correct result:

The only confusion left is why I had to change the data type from decimal to whole number for this to work.
But for now the issue is solved.