Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Remove Top/Bottom 50 rows by Value on Data Reports that are updated monthly

I am wondering if there is a way to remove the Top & Bottom 50 rows by a Value specified in one column.  

 

Few Main Points to consider:

  • The report updates monthly and are manually pulled into Power BI through refresh.
  • The Top/Bottom 50 will change monthly.
  • The Top/Bottom 50 are too high/low and effect the percentage outputs too greatly.. the reason they need to be removed.  
  • I plan on adding the top 50/bottom 50 into a new output for another page in the report, so if feasible I would like to be able to reverse engineer this solution.
  • Filtering out the top/bottom 50 on the report does not work, because it still affects the output on the overall percentage calculations
  • I am under the assumption that this has to be done either in Power Query or in the data source itself, however I would like to see if there a way to automize this process - I do not want to go in monthly and manually delete the top 50 rows.  

 

 

Thank you for your time and efforts here.


Regards,

 

Ryan Mulhollem

2 ACCEPTED SOLUTIONS
v-lili6-msft
Community Support
Community Support

hi,@ryan_mulhollem

    After my research, you can do these like below:

basic data

15.PNG

And i need to remove Top/Bottom 5 rows by Value

 

use this formula to create a new table

Table = var _count =COUNTROWS(Table1) return
var _table5=TOPN(_count-5,Table1,Table1[rank],ASC) return
var _table10=TOPN(_count-10,_table5,Table1[rank],DESC) return
_table10

Result:

16.PNG

 

here is demo,please try it.

https://www.dropbox.com/s/nutv50e6509jdq0/Remove%20TopBottom%2050%20rows.pbix?dl=0

 

Regards,

lin

 

 

Community Support Team _ Lin
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

Anonymous
Not applicable

Thank you for the reply.  As per checking, this formula did work.  I would also want to add for anyone else interested the formula to create a table for only the top 50:

 

OnlyTop50 =
var _table5=TOPN(50,table,table[column],DESC) return
_table5

 

Thank you!

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi,@ryan_mulhollem

    After my research, you can do these like below:

basic data

15.PNG

And i need to remove Top/Bottom 5 rows by Value

 

use this formula to create a new table

Table = var _count =COUNTROWS(Table1) return
var _table5=TOPN(_count-5,Table1,Table1[rank],ASC) return
var _table10=TOPN(_count-10,_table5,Table1[rank],DESC) return
_table10

Result:

16.PNG

 

here is demo,please try it.

https://www.dropbox.com/s/nutv50e6509jdq0/Remove%20TopBottom%2050%20rows.pbix?dl=0

 

Regards,

lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you for the reply.  As per checking, this formula did work.  I would also want to add for anyone else interested the formula to create a table for only the top 50:

 

OnlyTop50 =
var _table5=TOPN(50,table,table[column],DESC) return
_table5

 

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.