Forum Discussion

KAATA's avatar
KAATA
Helper I
2 years ago
Solved

RANKX returning duplicate values

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])
        )
    )

 




Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

  • KAATA's avatar
    KAATA
    2 years ago

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

    • KAATA's avatar
      KAATA
      Helper I
      2 years ago

      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.

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

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

    • KAATA's avatar
      KAATA
      Helper I
      2 years ago

      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.