Forum Discussion

jwdal's avatar
jwdal
Frequent Visitor
8 months ago
Solved

Top 10 excluding value

This formula kind of works:   In Top 10 = CALCULATE ([Net Sales], FILTER( VALUES( AC_ALL[Top10]), IF( RANKX( ALL( AC_ALL[Top10]), [Net Sales],,DESC) <= 10 && AC_ALL[Top10] <> "EXCLUDE", [Net S...
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    8 months ago

    Hi jwdal,
    Try this instead it will work:

    In Top 10 =
VAR CustomersWithoutExclude = 
    FILTER(
        ALL(AC_ALL[Top10]),
        AC_ALL[Top10] <> "EXCLUDE"
    )
VAR Top10Customers =
    TOPN(
        10,
        CustomersWithoutExclude,
        [Net Sales], DESC
    )
RETURN
    CALCULATE(
        [Net Sales],
        KEEPFILTERS(AC_ALL[Top10] IN Top10Customers)
    )
rohit1991's avatar
rohit1991
Super User
8 months ago

Hii jwdal 

 

When you exclude a customer after calculating the Top 10, you end up with only 9 rows because the blank slot is not refilled. The fix is to remove the excluded customer before ranking. Example:

Top 10 Excluding =
VAR CleanList =
FILTER(ALL(AC_ALL[Top10]), AC_ALL[Top10] <> "EXCLUDE")
RETURN
IF(
RANKX(CleanList, [Net Sales], , DESC) <= 10,
[Net Sales]
)

This way the ranking is done on a list where the excluded value is already removed, so Power BI always returns a full Top 10.