Forum Discussion

jwdal's avatar
jwdal
Frequent Visitor
8 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 rank all except a certain customer.  I tried to add another condition to the IF but if the customer was in the Top 10, I only got 9 results.
 
Thanks.
  • Jihwan_Kim's avatar
    Jihwan_Kim
    8 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 )

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    8 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 )

     

    • jwdal's avatar
      jwdal
      Frequent Visitor
      8 months ago

      This worked perfectly but I want the measure to return the sum of sales instead of the rank.

       

      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
    Super User
    8 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.