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