Forum Discussion

jwdal's avatar
jwdal
Frequent Visitor
9 months ago
Solved

RANKX excluding a value

In Top 10 = CALCULATE ([Net Sales], FILTER( VALUES( AC_ALL[Top10]), IF( RANKX( ALL( AC_ALL[Top10]), [Net Sales],,DESC) <= 10, [Net Sales], BLANK() )))   "Top10" is customer name.  I want to ra...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    9 months ago

    Hi,

    I do not know how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I tried to use WINDOW DAX function and RANK DAX function in the measures.

     

     

     

     

     

    Sales total = SUM(sales[sales])

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    RANK function (DAX) - DAX | Microsoft Learn

     

    Top 10 sales except C03 = 
CALCULATE (
    [Sales total],
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            10,
            ABS,
            FILTER ( ALL ( customer[customer_name] ), customer[customer_name] <> "C03" ),
            ORDERBY ( [Sales total], DESC )
        )
    )
)

     

    Rank = 
VAR _result =
    RANK (
        SKIP,
        FILTER ( ALL ( customer[customer_name] ), customer[customer_name] <> "C03" ),
        ORDERBY ( [Sales total], DESC )
    )
RETURN
    IF ( _result <= 10, _result )

     

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Icon for Super User rankSuper User
9 months ago

Hi jwdal,

I hope you are doing well today ☺️❤️

 

You can try this on of these DAX Approaches it should work with you :

First Approach is faltering Out the Customer First, Then Rank

Top 10 Excluding Specific Customer =
VAR ExcludedCustomer = "Customer Name To Exclude"  // Replace with actual customer name
VAR CustomersToRank =
    FILTER(
        ALL(AC_ALL[Top10]),
        AC_ALL[Top10] <> ExcludedCustomer
    )
VAR RankedCustomers =
    ADDCOLUMNS(
        CustomersToRank,
        "Rank", RANKX(CustomersToRank, [Net Sales],, DESC)
    )
VAR Top10Customers =
    FILTER(
        RankedCustomers,
        [Rank] <= 10
    )
RETURN
    CALCULATE(
        [Net Sales],
        Top10Customers
    )

 

Second Approach using CALCULATETABLE for Cleaner Logic (Recommended)

Top 10 Excluding Specific Customer =
VAR ExcludedCustomer = "Customer Name To Exclude"
VAR CustomersWithoutExcluded =
    CALCULATETABLE(
        VALUES(AC_ALL[Top10]),
        ALL(AC_ALL[Top10]),
        AC_ALL[Top10] <> ExcludedCustomer
    )
VAR Top10Customers =
    TOPN(
        10,
        CustomersWithoutExcluded,
        [Net Sales],
        DESC
    )
RETURN
    CALCULATE(
        [Net Sales],
        Top10Customers
    )

 

Bonus Approach If You Want a Boolean Measure for Filtering

Is Top 10 Excluding Specific Customer =
VAR ExcludedCustomer = "Customer Name To Exclude"
VAR CurrentCustomer = SELECTEDVALUE(AC_ALL[Top10])
RETURN
    IF(
        CurrentCustomer = ExcludedCustomer,
        FALSE(),
        VAR CustomersWithoutExcluded =
            FILTER(
                ALL(AC_ALL[Top10]),
                AC_ALL[Top10] <> ExcludedCustomer
            )
        VAR CustomerRank =
            RANKX(
                CustomersWithoutExcluded,
                [Net Sales],
                ,
                DESC
            )
        RETURN
            CustomerRank <= 10
    )

 

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.