Top N slicer to be added on dashboard to filter table values

[harshadrokade]

Hi,

Thanks for looking into the query.

I have a table with date wise values of Sales of the client names. (Client name, Date, Sales)

I have created a dax measure as below that gives me YTD numbers as per year selected in Year slicer. Years coming from Master calendar table and that table is connected with Sales table, Date column.

1.YTD Net amt =

VAR MaxDate = today()

VAR crrentyear = SELECTEDVALUE('Master Calendar'[year])

RETURN

            CALCULATE(

        SUM('SalesTable'[Net_Amount]),

        MONTH('Master Calendar'[Date]) <= MONTH(today()),

        YEAR('Master Calendar'[Date])=crrentyear )

I am showing a Table visual on dashboard with Client names and their YTD numbers.

I want to add a slicer (with values as Top10 or All) that will show only Top 10 client names (based on max top 10 YTD numbers) if Top10 is selected and shows All clients if All is selected slicer..

[v-yangliu-msft]

Hi  @harshadrokade ,

Here are the steps you can follow：

1. Enter data – create a table.

2. Create measure.

Rank =
RANKX(
    ALLSELECTED('SalesTable'),[1.YTD Net amt],,DESC,Dense)

Flag =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
SWITCH(
    TRUE(),
    _select="Top10" && [Rank]<=10,1,
    _select="All" && [Rank]>=1,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Select Top10:

Select All:

Best Regards,

Liu Yang

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

[harshadrokade]

Thanks @v-yangliu-msft . The slicer is not filtering the table as expected. can you pls check

[harshadrokade]

@MFelix can u pls help here

[MFelix]

Hi @harshadrokade ,

In this case you need to create a table for the disconnected slicer then you need to create a measure that picks up that value something similar to this:

Ranking =
SWITCH (
    SELECTEDVALUE ( 'TOPN'[TOPNSelection] ),
    "Top 10",
        IF (
            RANKX (
                ALLSELECTED ( Customer[Customer] ),
                [Sales],
                [Sales],
                DESC
            ) <= 10,
            1
        ),
    1
)

Then use this metric on the visual and select on when is not blank.

[harshadrokade]

Thanks @MFelix . Ther is no disconnected table. The Sales table and master calendar are connected and so I am not sure how to follow your instructions.

[MFelix]

The disconnected table is a new one that you need to create for doing the slicer nothing more. That table only contais two values All and Top 10.

[harshadrokade]

Thanks @MFelix 

What do you mean by Then use this metric on the visual and select on when is not blank.

Where exactly I use this newly created measure on visual? and what do you mean by when its not blank?

Pls help

[MFelix]

@harshadrokade

Just add it to your visual filter in the filter pane, then the option you select is the advanced is not blank. When you apply that filter the visualization gets filter out. 

[harshadrokade]

OK. Will try. Thanks

[MFelix]

If you are not abble to achieve necessary result please let me know and I will send you a PBIX file for test.

[harshadrokade]

Thanks @MFelix. I could get the results partially but facing an issue in the drill downs.

I forgot to mention that I have one more column as Sub client name. So I have columns as below in Sales table-

1. Client name
2. Sub client name
3. Date
4. Sales

A measure is also creaed to get YTD sales as 1.YTD Net amt

Sharing steps that I followed-

1) Created a disconnected Slicer table with values as Top10 and All

2) Created a measure as below to get the rank on Client name level based on YTD numbers

Rank =

RANKX(

    ALL('Sales table'[Client Name]),[1.YTD Net amt],,DESC,Dense)

3) Created one more measure that will be used in filter of the column chart

Rank Flag =

var _select=SELECTEDVALUE('Slicer_Table'[Slicer])

return

SWITCH(

    TRUE(),

    _select="Top10" && [xRank]<=10,1,

    _select="All" && [Rank]>=1,1,0)

4) Added a column chart visual on dashboard with Client name on X axis, YTD number on Y axis. Added a filter of Rank Flag on visual with value is equal to 1

5) Added a slicer visual on dashboard with values from Slicer table.

Now the slicer properly filters the chart when I selected Top 10 or All.

The problem appears when I add a drilldown of Sub client name to the chart. When I add Sub client name as well to the X axis (Below Client name), the slicer doesnt filter the visual properly.

I want the visual to keep showing Top10 Client names even if I drill down to Sub client name on column chart visual but when I add Sub client name to visual for drill down, the visualm starts showing all Client names even if Top10 is selected on slicer.

Thanks again for all your help!!

[harshadrokade]

Hi @MFelix - Will you be able to help on this query?

[MFelix · Accepted solution]

Hi @harshadrokade ,

Try the following code:

New Ranking = 
SWITCH (
    SELECTEDVALUE ( Slicer_Table[Slicer] ),
    "Top10",
        IF (
           RANKX (
                ALLSELECTED(  SalesTable[Client]),
                CALCULATE([1.YTD Net amt],ALLEXCEPT(SalesTable, SalesTable[Client]))
                ,
                ,
            ) <= 5,
            1
        ),
    1
)

File attach.

[harshadrokade]

All works fine when I remove my existing relationship between Sales table and master calendar table but when I keep it, the top 10 filter doesnt work. I cant remove it as it filters few other charts on dashboard.

[MFelix]

I have used the file you have sent that did not had any relationship so I did not even made any change to that part of the formula, try to chenge your YTD to the following:

1.YTD Net amt = 

    TOTALYTD(SUM([Net_Amount]), 'Master Calendar'[Date])

[harshadrokade]

U r awesome. Thanks for ur valuable time and support 🙂

[harshadrokade]

@MFelix waiting for ut help 🙂

[harshadrokade]

Hi  v-rongtiep-msft, Can u pls here here