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 )

     

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

 

  • jwdal's avatar
    jwdal
    Frequent Visitor
    9 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 )