Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SW_Srinivas
Frequent Visitor

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
 
1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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
    )
)
v-shex-msft
Community Support
Community Support

Hi @SW_Srinivas,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors