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'

amitchandak's avatar
amitchandak
Super User
8 months ago

jwdal , Create a measure with what ever you want to exlcude

Filter M = CALCULATE([Net Sales], keepfilters(AC_ALL[Top10] <> "EXCLUDE"))


Top 10 = CALCULATE([Filter M], KEEPFILTERS(TOPN(10, ALL( AC_ALL[Top10]), [Filter M], DESC)))


Top 10 = CALCULATE([Filter M], KEEPFILTERS(Window(0,ABS,10,ABS, ALL( AC_ALL[Top10]),ORDERBY([Filter M],Desc))))

 

or 

 

Top 10 = CALCULATE([Filter M], KEEPFILTERS(TOPN(10, allselected( AC_ALL[Top10]), [Filter M], DESC)))


Top 10 = CALCULATE([Filter M], KEEPFILTERS(Window(0,ABS,10,ABS, allselected( AC_ALL[Top10]),ORDERBY([Filter M],Desc))))