Forum Discussion
Top 10,20,30 Values using Dynamic Slicer
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
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.
6 Replies
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.
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"
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.
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?
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
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 helpRegards
Ashish
