Top 10,20,30 Values using Dynamic Slicer

[Ashish_kumar12]

Hi There ,

I am creating a report where i have data in table which includes column like  (Unit Sold, Revenue, Order Date ,Product Id ,Product Name ) and i am trying to create dynamic table where i have a slicer of Top 10 ,20,30,50,500 but i want to filter my data when i clicked on Top 10 so it will give me top 10 productsID, Product Name and Revenue and similarly if i click on Top 20 it will shows me data for Top 20 productid, product name and revenue .

Please see below sample data for more understanding :
Table 1

Order Date	Product Id	Product Name	Revenue	Untis Sold
05/16/2021	110490	Sunslik	5.6	15
05/17/2021	1307555	Jelly Water	70.5	111
05/18/2021	1420880	Hair Removal	100.5	145
05/19/2021	1421935	Lubricant	15	12
05/20/2021	1373935	Airborne	145.45	45

Please see the attached file  for more calrifaction and if anyone can help me out in this ?

Thank you in advance ,

Ashish 

[Anonymous · Accepted solution]

Hi  @Ashish_kumar12 ,

Here are the steps you can follow：

1. Create a table.

2. Create measure.

Measure_rank =
RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Revenue])),,DESC)

Measure_flag =
var _top=SELECTEDVALUE(Slice[Top])
return
IF([Measure_rank]<=_top,1,0)

3. Set Sort by-[ Measure_rank] and Sort ascending.

4. Take the [Top] column of the Slice table as the slicer, put measure[Measure_flag] into the Filter, and set is=1, apply filter.

5. Result.

When 10 is selected, the top 10 data will be displayed:

When 20 is selected, the top 20 data will be displayed:

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.

[Harika05]

A small query - I have tried to apply this solution,my dimension is a field parameter with 4 columns coming from table 'Facts' ,I when I apply the same logic its not giving correct results .

Measure_Rank=

RANKX(ALLSELECTED('Facts'),abs([CM]-[LM]),,DESC)  anything to do differently?

[Anonymous]

Hi  @Ashish_kumar12 ,

Here are the steps you can follow：

1. Create a table.

2. Create calculated column

rank = RANKX('Table','Table'[Revenue],,DESC)

3. Create measure.

Flag =
var _top=SELECTEDVALUE(Slice[Top])
return
IF(MAX('Table'[rank])<=_top,1,0)

4. Set Sort by-rank and Sort ascending.

5. Take the [Top] column of the Slice table as the slicer, put measure[Flag] into the Filter, and set is=1, apply filter.

6. Result.

When 10 is selected, the top 10 data will be displayed:

When 20 is selected, the top 20 data will be displayed:

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.

[Ashish_kumar12]

Hi Liu,

Thank you so much for your response .

I tried the same measures which you have suggested and i got this error:

Also when i checked my Rank measure i got the same value in each row "1"

[amitchandak]

@Ashish_kumar12 , You can create TOPN with help from what if

example

measure =

var _n = selectedvalue(whatif[param])

return

CALCULATE([Revenue],TOPN(_n,allselected(Table[productsID]),[Revenue],DESC),VALUES(Table[productsID]))

refer

https://www.youtube.com/watch?v=UAnylK9bm1I

[Ashish_kumar12]

Hi Amit ,

Thank you for your response .

I tried your method but it didn't work , so i found this :

https://www.youtube.com/watch?v=QtEt-QI3oe4

that's what i was looking for ,But thank you so much for your help 

Regards

Ashish