how to do a top n filter and restrict specific ite…

by


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

Emma



Source link

Leave a Comment