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. Ca...
  • 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")
        )
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")
    )
      • techies's avatar
        techies
        Icon for Super User rankSuper User
        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")
            )