Forum Discussion

lucie_raboch's avatar
lucie_raboch
Icon for Helper II rankHelper II
1 year ago
Solved

RANKX doesnt work as It should

Hi all,

My goal is to show top 20 master by metric Sales and the rest will be grouped like 'Others'. 

I'm working with RANKX to be able rank values, but it is showing 11111 insted of 1,2,3,4,5.

Can you tell me what I'm doing wrong please?

 

Result (excel version)

 

 

This is my Power BI file: https://drive.google.com/file/d/1HagYTmRAdT6SFQd1HXw7CM10jwrFFOcy/view?usp=sharing

 

Thank you for your help on this issue.

Lucie

  • techies's avatar
    techies
    1 year ago

    Hi lucie_raboch based on my understanding on the data model, here is the calculated table , please test it if it works, not able to upload pbix file

     

    TopMasterTable =
    VAR BaseTable =
        SUMMARIZE(
            'Master_Unique',
            'Master_Unique'[Master]
        )

    VAR SalesWithValues =
        ADDCOLUMNS(
            BaseTable,
            "Sales",
            CALCULATE(
                SUM('FactVarMetrics'[Value]),
                'DimSce'[Scenario] = "Budget",
                'DimSce'[Metric] = "Gross",
                TREATAS(VALUES('Master_Unique'[Master]), 'DimProducts'[Master])
            )
        )

    VAR WithRank =
        ADDCOLUMNS(
            SalesWithValues,
            "Sales Rank",
            RANKX(
                SalesWithValues,
                [Sales],
                ,
                DESC,
                DENSE
            )
        )

    RETURN
        ADDCOLUMNS(
            WithRank,
            "MasterGroup",
            IF([Sales Rank] <= 20, [Master], "Others")
        )

9 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi lucie_raboch ,

     

    To create a proper ranking for your "Master" by Sales in Power BI and group everything outside the Top 20 as "Others", you need to use RANKX in a way that evaluates all values in the dataset regardless of what’s currently being filtered by the visual. The reason you were getting only 1s is likely because the RANKX function was evaluating over a single visible row context due to the table visual.

    You can fix it by first calculating the ranking using the ALL function inside RANKX to remove any filters on the "Master" column. Here's the corrected measure:

    Ranking = 
RANKX(
    ALL('YourTable'[Master]),
    CALCULATE(SUM('YourTable'[Sales])),
    ,
    DESC,
    DENSE
)

    To create a grouping for Top 20 and "Others", define a new column or measure like this:

    Master Group = 
VAR CurrentRank = 
    RANKX(
        ALL('YourTable'[Master]),
        CALCULATE(SUM('YourTable'[Sales])),
        ,
        DESC,
        DENSE
    )
RETURN 
    IF(CurrentRank <= 20, 
        SELECTEDVALUE('YourTable'[Master]), 
        "Others"
    )

    Then you can use Master Group as the row field in your matrix and sum of Sales as the value. This will show the Top 20 masters individually and group everything else under "Others".

    If you want to make sure “Others” always shows up at the bottom, create a sort order measure like this:

    Sort Order = 
VAR CurrentRank = 
    RANKX(
        ALL('YourTable'[Master]),
        CALCULATE(SUM('YourTable'[Sales])),
        ,
        DESC,
        DENSE
    )
RETURN 
    IF(CurrentRank <= 20, CurrentRank, 999)

    Use this to sort the "Master Group" column. This ensures that "Others" comes last in your visual.

     

    Best regards,

  • lucie_raboch's avatar
    lucie_raboch
    Icon for Helper II rankHelper II
    1 year ago

    Thank you for your answer, but your solution doesnt work, still getting 1,1,1,1,1

     

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi lucie_raboch for ranking you can use this 

     

    Rank by Sales =
    RANKX(
        ALL('Sheet9'[Master]),
        [Sales amount],
        , DESC,
        DENSE
    )

     

    where [sales amount] is a measure as this-- 

    Sales amount = SUM('Sheet9'[Sales])
  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi lucie_raboch please check this measure for grouping

     

    Master Group =
    VAR CurrentRank =
        RANKX(
            ALL('Sheet9'[Master]),
            [Sales amount],
            , DESC,
            DENSE
        )
    RETURN
    IF(CurrentRank <= 20, SELECTEDVALUE('Sheet9'[Master]), "Others")
     

     

  • lucie_raboch's avatar
    lucie_raboch
    Icon for Helper II rankHelper II
    1 year ago

    Hi,

    Ranking is working now, but I need to remove Master from the table to see only top 20 rows and Others and if I remove Master Column then Master Group is BLANK.

    Also how can I sort according to the Rank?

    • techies's avatar
      techies
      Icon for Super User rankSuper User
      1 year ago

      Hi lucie_raboch does creating a calculated table and using its columns in a table visual work for you?

      like this

       

      TopMasterTable =
      ADDCOLUMNS(
          SUMMARIZE('Sheet9', 'Sheet9'[Master]),
          "Sales", [Sales amount],
          "Sales Rank", RANKX(
              ALL('Sheet9'[Master]),
              [Sales amount],
              ,
              DESC,
              DENSE
          ),
          "MasterGroup",  
          VAR CurrentRank =
              RANKX(
                  ALL('Sheet9'[Master]),
                  [Sales amount],
                  ,
                  DESC,
                  DENSE
              )
          RETURN
              IF(CurrentRank <= 20, 'Sheet9'[Master], "Others")
      )