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)
    )
jwdal's avatar
jwdal
Frequent Visitor
8 months ago

Got an error message 

 

Cannot find table 'Top10Customers'

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