Top/Bottom/All on table based on slicer selection

[Anonymous]

Hi Everyone,

i have a table visual which has Account Name, current period revenue and previous period revenue as values. 
I need to give 2 slicers .

1) to select Top/Bottom/All

2) to select number between 2 and 30

Now based on the slicer selections, my table visual should show change.

eg: if I choose All then it should show all Account Name.

if Top and 5 is selected, then table should show top 5 accounts with highest current period revenue. And similar for Bottom as well.

My current solution involves creating the below measure and adding it as Visual level filter :

Top/Bottom Account =

VAR NValue =

    SELECTEDVALUE ( 'Drop Down - Account'[Values] )

VAR Ranking_TOP_CUST =

    CALCULATE(RANKX (

        ALLSELECTED ( 'Sales'[Account Name] ),

        [Current Period Revenue],

        ,

        DESC

    ),KEEPFILTERS('SalesGenie VN'[Account Name] IN { "Trading/Sub-Dealer" } = FALSE))

VAR Ranking_Bottom_CUST =

    RANKX (

        ALLSELECTED ( 'Sales'[Account Name] ),

        [Current Period Revenue],

        ,

        ASC

    )

VAR Ranking_All_CUST =

    RANKX (

        ALLSELECTED ( 'Sales'[Account Name] ),

        [Current Period Revenue],

        ,

        ASC

    )

VAR Result =

    SWITCH (

        SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ),

        "Top", INT ( Ranking_TOP_CUST <= NValue ),

        "Bottom", INT ( Ranking_Bottom_CUST <= NValue ),

        "All", 1

    )

RETURN

    Result

I wanted to check if there is an alternate solution to achieved the same.

Thanks in advance..

Swathi

@tamerj1 @Greg_Deckler 

[wdx223_Daniel · Accepted solution]

Top/Bottom Account =
SWITCH (
    SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ),
    "All", 1,
    "Top",
        IF (
            MAX ( 'Sales'[Account Name] )
                IN CALCULATETABLE (
                    VALUES ( 'Sales'[Account Name] ),
                    TOPN (
                        SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
                        ALLSELECTED ( 'Sales'[Account Name] ),
                        [Current Period Revenue], DESC
                    )
                ),
            1
        ),
    IF (
        MAX ( 'Sales'[Account Name] )
            IN CALCULATETABLE (
                VALUES ( 'Sales'[Account Name] ),
                TOPN (
                    SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
                    ALLSELECTED ( 'Sales'[Account Name] ),
                    [Current Period Revenue], ASC
                )
            ),
        1
    )
)

[Anonymous]

Hi @Anonymous,

Perhaps you can take a look at the following link that also talked about dynamic top/bottom N if it helps for your requirement:

Solved: Dynamic Top/Bottom N Slicer - Almost have it! - Microsoft Power BI Community
Regards,

Xiaoxin Sheng