default slicer top N value

[Anonymous]

Hi All,

I have a table visual and a slicer to so select the top N value, How can i default the slicer to show top15 please?

Thanks 

Ammy

[Anonymous · Accepted solution]

Hi Ammy,

We can do the same dynamically, so the user can select top value.

Please follow the following steps:

Create the following measure for calculating the rank:

Rank By Amount =

RANKX(ALL('Table_Name'[comp_name]), [amount],,DESC)

Then create a table with one column. (You can use Enter Data option in Power BI)

For ex use the name Top for both table name and column name.

Insert values like 10, 20, 30, ....  200.

Use this table values for filtering dynamically.

Then create the second measure:

Selected Top N Value = SELECTEDVALUE('Top'[Top])

Then create one more measure measure:

comp_name Included = IF([Rank By Amount] <= [Selected Top N Value],1,0)

And add this measure in Filters pane and set the value equal to 1.

Please refer the following image for reference.

[Anonymous]

Hi

In this exmple is it deafulting to top 10 ? Thanks 

[Anonymous]

You can add one more measure for default value.

Selected Top N Value New = IF(HASONEVALUE('Top'[Top]), SELECTEDVALUE('Top'[Top]), MIN('Top'[Top]))

Also update the previous measure

comp_name Included = IF([Rank By Amount] <= [Selected Top N Value New],1,0)

[Mariusz]

Hi @Anonymous 

To show a slicer with top N, you need to create a Rank Measure like the example below.

Rank Brand = 
RANKX(
    CALCULATETABLE(
        VALUES( 'Product'[Brand] ), 
        ALLSELECTED()
    ), 
    CALCULATE(
        SUMX(Sales, Sales[Net Price] * Sales[Quantity])
    ),, 
    DESC
) 

and use it as a Visual filter as below

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi 

Thanks for your help.

In this case if i use visual filter for the slicer and restict it by setting the  "is less than or equal to" = 15, 

then the slicer does not allow me to go more than rank 15 , i have upto 200 rank, which the user will also want to see.

Just deafulting it to the top 15 in the table visual. Please suggest how to achive that.

Thanks  

[Mariusz]

Hi @Anonymous 

Can you share some data sample and explain your scenario based on that.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

i have rank morethan 200, i just got untill 27 as sample ,

comp_name	amount	rank
Customer Requested	91,987	1
Listing Miscellaneous Outright - 2011	34,567	2
White Pages	33,985	3
Print Credit - 2012	29,000	4
White Pages Customised 2012	12,345	5
Reversal On Answer	1	6
Horn Loud Speaker	1	7
Disabilities	1	8
Disabilities -drf	1	9
dggfs	11,345	10
T-Hub 2  hkjeckns	11,305	11
T-Hub 2 admint	10,305	12
T-Hub 2  separate	10,000	13
T-Hub 2  hkjeckns2	9,305	14
T-Hub 2  hkjeckns4	9,205	15
T-Hub 2  hkjeckns7	8,688	16
T-Hub 2  hkjeckns8	8,543	17
T-Hub 2  hkjeckns9	7,654	18
T-Hub 2  hkjeckns0	7,432	19
T-Hub 2  hkjeckns3	7,321	20
T-Hub 2  hkjeckns1	7,167	21
T-Hub 2  solumna 34	5,999	22
T-Hub 2  solumna 35	5,987	23
T-Hub 2  solumna 36	5,900	24
T-Hub 2  solumna 37	4,567	25
T-Hub 2  solumna 38	3,724	26
T-Hub 2  solumna 39	625	27

in the report i want the slicer value to default to top 15, but user can still slide the slicer and chnge it to more than 15 or less than 15,