how to do a top n filter and restrict specific items from being included

[emma313823]

All,

I have a table matrix and I've restricted it to the current year and one salesperson used as a bucked called New Business Development.

I want only the top 50 accounts by sales. The problem is we have some buckets we drop certain sales to and don't want this included in the results.

The following are the items we don't want included (and they exist for each territory we have and show exactly as shown).

U.S. government agencies / Metro
U.S. Government agencies / Mid
U.S. Government Agencies / NE
U.S Government Agencies / PA
U.S. Government Agencies / UNY
Educational Agencies / Metro
Educational Agencies / Mid
Educational Agencies / NE
Educational Agencies / PA
Educational Agencies / UNY
Individual Customer Names / Metro
Individual Customer Names / Mid
Individual Customer Names / NE
Individual Customer Names / PA
Individual Customer Names / UNY

I'm working in an inherited PBI model. The data is coming from the Billings table using End Customer and Sales. 

Under filter for End Customer, I did my Top N and it shows me the top 50, but I need to exclude the items above. Can someone provide some help on how to achieve this or if it can even be done?

[emma313823]

so i created a quick table and just pulled from Billings table End Customer and Sales.

I restricted my filters by Account Manager of NBD. For End Customer I did a filter for the Top 50 by sales, but results are including these accounts previously noted (there may be a few more, but I can add them once I figure this out).

I've attached a dropbox link for access to the exported data in .csv format.

NBD Top 50 Accounts by sales export 

[Anonymous]

Hi @emma313823 

Underscores are used to avoid unavailability of variables due to special characters.

You can change the name to what you want.

Is your [sales] a measure?

If you can, provide some virtual data and expected results. It is best presented in tabular form.

Regards,

Nono Chen

[Anonymous]

Hi @emma313823 

For your question, here is the method I provided:

Here's some dummy data

“Table”

Here I show the Top 5 sales. You can make changes based on your table name and field.

Top 5 Sales = 
var _Customer = SELECTEDVALUE('Table'[End Customer])
var _rank = 
    RANKX(
        FILTER(
            ALL('Table'),
            NOT 'Table'[End Customer]  IN {"A", "C", "D"}
        ),
        CALCULATE(SUM([Sales])),
        ,
        DESC,
        Dense
    )
RETURN 
IF(
    NOT _Customer IN {"A", "C", "D"}
    &&
    _rank <= 5,
    SELECTEDVALUE('Table'[Sales]),
    BLANK()
)

Alternatively, you can filter directly in the filters pane.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[emma313823]

Hi

After modifying to input my table names, I have this:

TopNBDbySales =

var _Customer = SELECTEDVALUE('Billings'[End Customer])

var _rank =

    RANKX(

        FILTER(

            ALL('Billings'),

            NOT 'Billings'[End Customer]  IN {"U.S. Government Agencies / Metro", "U.S. Government Agencies / Mid", "U.S. Government Agencies / NE", "U.S. Government Agencies / PA", "U.S. Government Agencies / UNY", "Educational Agencies / Metro", "Educational Agencies / Mid", "Educational Agencies / NE", "Educational Agencies / PA", "Educational Agencies / UNY", "Individual Customer Names / Metro", "Individual Customer Names / Mid", "Individual Customer Names / NE", "Individual Customer Names / PA", "Individual Customer Names / UNY"}

        ),

        CALCULATE(SUM([Sales])),

        ,

        DESC,

        Dense

    )

RETURN

IF(

    NOT _Customer IN {"U.S. Government Agencies / Metro", "U.S. Government Agencies / Mid", "U.S. Government Agencies / NE", "U.S. Government Agencies / PA", "U.S. Government Agencies / UNY", "Educational Agencies / Metro", "Educational Agencies / Mid", "Educational Agencies / NE", "Educational Agencies / PA", "Educational Agencies / UNY", "Individual Customer Names / Metro", "Individual Customer Names / Mid", "Individual Customer Names / NE", "Individual Customer Names / PA", "Individual Customer Names / UNY"}

    &&

    _rank <= 50,

    SELECTEDVALUE('Billings'[Sales]),

    BLANK()

)

I'm getting this however:

And could you explain why the underscore symbol is used in the two variables?

var _Customer = SELECTEDVALUE('Billings'[End Customer])

var _rank =

[emma313823]

A bit more info...I found some DAX and am hoping this may work, but am stuck on the modifications.

Here is what I found.

Top 10 Growth % = 
VAR _selcompany =
    SELECTEDVALUE ( 'Companies'[Company] )
VAR _rank =
    RANKX (
        FILTER (
            ALLSELECTED ( 'Companies'[Company] ),
            NOT Companies[Company] IN { "Company A", "Company B" }
        ),
        [Net Sales Growth %]
    )
RETURN
    IF (
        NOT _selcompany
            IN { "Company A", "Company B" }
            && _rank <= 10,
        [Net Sales Growth %],
        BLANK ()
    )

Here is how I started to modify it: I highlighted in red what I am unsure of.

TopN =
VAR -selcompany = (not sure if i need to change this)
SelectedValue ('Billings'[End Customer])
VAR _rank = (I guess i do want to rank, but top 50 by sales)
Rancx(
Filter (
AllSELECTED ('Billings'[End Customer]),
NOT Billings[End Customer] IN {"U.S. Government Agencies / Metro", "U.S. Government Agencies / Mid", "U.S. Government Agencies / NE", "U.S. Government Agencies / PA", "U.S. Government Agencies / UNY", "Educational Agencies / Metro", "Educational Agencies / Mid", "Educational Agencies / NE", "Educational Agencies / PA", "Educational Agencies / UNY", "Individual Customer Names / Metro", "Individual Customer Names / Mid", "Individual Customer Names / NE", "Individual Customer Names / PA", "Individual Customer Names / UNY"}
),
[Sales]
)
Return
IF (
Not -selcompany
IN {"U.S. Government Agencies / Metro", "U.S. Government Agencies / Mid", "U.S. Government Agencies / NE", "U.S. Government Agencies / PA", "U.S. Government Agencies / UNY", "Educational Agencies / Metro", "Educational Agencies / Mid", "Educational Agencies / NE", "Educational Agencies / PA", "Educational Agencies / UNY", "Individual Customer Names / Metro", "Individual Customer Names / Mid", "Individual Customer Names / NE", "Individual Customer Names / PA", "Individual Customer Names / UNY"}
&& _rank ,<= 10,
[Sales], (i switched this to sales based on my table)
blank()
)