cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan_mulhollem Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

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

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.
ryan_mulhollem Regular Visitor
Regular Visitor

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

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!

2 REPLIES 2
Community Support Team
Community Support Team

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

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.
ryan_mulhollem Regular Visitor
Regular Visitor

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

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!