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