topic Re: Top 10 excluding value in DAX Commands and Tips

Top 10 excluding value

jwdal — Mon, 08 Dec 2025 14:32:26 GMT

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 Sales], BLANK() )))

If the excluded customer is in the top 10, I only get 9 hits.

Re: Top 10 excluding value

FBergamaschi — Mon, 08 Dec 2025 14:37:33 GMT

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

Re: Top 10 excluding value

rohit1991 — Mon, 08 Dec 2025 14:38:16 GMT

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.

Re: Top 10 excluding value

Ahmed-Elfeel — Mon, 08 Dec 2025 15:38:17 GMT

Hi jwdal,

The Issue here is that when the excluded customer is in the top 10 you are ranking only 9 customers with ranks 1-9 but checking for ≤10 so you only get 9 results....Here are two better Approaches you can try:

First Approach:Rank first then exclude (most robust)

In Top 10 = VAR AllCustomers = ALL(AC_ALL[Top10]) VAR ExcludedValue = "EXCLUDE" VAR RankedCustomers = ADDCOLUMNS( AllCustomers, "Rank", RANKX(AllCustomers, [Net Sales], , DESC), "Sales", [Net Sales] ) VAR Top10Customers = FILTER( RankedCustomers, [Rank] <= 10 && [Top10] <> ExcludedValue ) VAR ResultCustomers = TOPN( 10, Top10Customers, [Sales], DESC ) RETURN CALCULATE( [Net Sales], TREATAS(VALUES(SELECTCOLUMNS(ResultCustomers, "Customer", [Top10])), AC_ALL[Top10]) )

Second Approach:Using TOPN with exclusion (cleanest and most efficient one)

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 VALUES(Top10Customers[Top10])) )

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Top 10 excluding value

jwdal — Mon, 08 Dec 2025 15:59:11 GMT

Got an error message

Cannot find table 'Top10Customers'

Re: Top 10 excluding value

amitchandak — Mon, 08 Dec 2025 16:10:36 GMT

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))))

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))))

Re: Top 10 excluding value

wardy912 — Mon, 08 Dec 2025 16:26:36 GMT

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!

Re: Top 10 excluding value

Ahmed-Elfeel — Mon, 08 Dec 2025 17:25:10 GMT

Hi jwdal,
Try this instead it will work: