cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ashish_kumar12
Helper I
Helper I

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 1Sample_Canvas.JPG

Order Date Product Id Product NameRevenueUntis Sold
05/16/2021110490Sunslik5.615
05/17/20211307555Jelly Water70.5111
05/18/20211420880Hair Removal100.5145
05/19/20211421935Lubricant 1512
05/20/20211373935Airborne145.4545

 

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

 

Thank you in advance ,

Ashish 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Ashish_kumar12 ,

Here are the steps you can follow:

1. Create a table.

v-yangliu-msft_0-1622536462966.png

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.

v-yangliu-msft_1-1622536462974.png

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.

v-yangliu-msft_2-1622536462977.png

5. Result.

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

v-yangliu-msft_5-1622536545083.png

 

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

v-yangliu-msft_6-1622536545086.png

 

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.

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @Ashish_kumar12 ,

Here are the steps you can follow:

1. Create a table.

v-yangliu-msft_0-1622536462966.png

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.

v-yangliu-msft_1-1622536462974.png

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.

v-yangliu-msft_2-1622536462977.png

5. Result.

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

v-yangliu-msft_5-1622536545083.png

 

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

v-yangliu-msft_6-1622536545086.png

 

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.

View solution in original post

v-yangliu-msft
Community Support
Community Support

Hi  @Ashish_kumar12 ,

Here are the steps you can follow:

1. Create a table.

v-yangliu-msft_0-1621996944066.png

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.

v-yangliu-msft_1-1621996944083.png

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

v-yangliu-msft_2-1621996944087.png

6. Result.

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

v-yangliu-msft_3-1621996944090.png

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

v-yangliu-msft_4-1621996944094.png

 

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:

Ashish_kumar12_0-1622460070848.png

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

Ashish_kumar12_1-1622460129236.png

 

amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors