Top 10 excluding value

[jwdal]

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.

[wardy912]

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!

[Ahmed-Elfeel]

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.

[jwdal]

Got an error message 

Cannot find table 'Top10Customers'

[Ahmed-Elfeel · Accepted solution]

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

[amitchandak]

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

[rohit1991]

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.

[FBergamaschi]

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