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)
    )
wardy912's avatar
wardy912
Super User
8 months ago

Hi jwdal 

 

 Give this a try:

 

Top 10 (excluding "EXCLUDE") =
VAR Top10CustomersExcluding =
    TOPN(
        10,
        FILTER(
            ALL(AC_ALL[Top10]),         -- remove current filters on the attribute
            AC_ALL[Top10] <> "EXCLUDE"  -- exclude first
        ),
        [Net Sales],
        DESC
    )
RETURN
CALCULATE(
    [Net Sales],
    KEEPFILTERS(Top10CustomersExcluding)  -- apply the TopN set to the current context
)

 

ALL(AC_ALL[Top10]) resets any filtering on that column so ranking is global.
The FILTER removes "EXCLUDE" before the ranking happens.
TOPN then selects the Top 10 from those remaining.
KEEPFILTERS keeps this as a narrowing filter so it plays nicely in visuals.

 

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

 

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!