Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Top/Bottom/All on table based on slicer selection

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
 
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    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
    )
)

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago
    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
    )
)