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. ...
  • lbendlin's avatar
    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.