find top and bottow N rows in table (DAX)

[Anonymous]

Hi

how to find top and bottom N rows . Below given queries are correct or not?

1.Top N rows

   Top N res = IF(HASONEVALUE('Top'[TopN]),
   IF(RANKX(ALL(Res[Res_Name]),[Total N rows])<=VALUES('Top'[TopN]),[Total N rows],BLANK()),
   [Total N rows])

2. Bottom N rows

   Bottom N res = IF(HASONEVALUE('Bottom'[Bottom N]),
   IF(RANKX(ALL(Res[Res_Name]),[Total N leaves])>VALUES(‘Bottom’[Bottom N]),[Total N rows],BLANK()),
   [Total N rows])

[araza]

Here is the sample data I threw together.

On top of that, I created a simple measure for the Total Amount.

Total Amount = SUM(Data[Amount])

With that I created a new measure that will essentially flag each row as being in the Top or Bottom 3 (you can change the number to meet your needs).

This measure first checks if there is a value for Total Amount and "removes" any that have a blank value ("removes" by making the flag blank and thus will never be included in any filtering or such).

TopBottom = IF( ISBLANK([Total Amount]), BLANK(), IF( RANKX(ALL(Data), [Total Amount], , ASC) <= 3 || RANKX(ALL(Data), [Total Amount], , DESC) <= 3, 1, 0 ) )

Once you have the ranking flag measure, you can add it to your visual and then filter to where the measure is 1.

Once that is all finished, you should have a visual only showing the entries you care about. Here is the full list of data with the flag visible and the resulting table when applying the filter.

Reference From:

Here

Regards,

Ahmed Raza

[v-jianhe-msft]

Hi,

May I know how is this issue going currently? 

BR,

Henry 

[Anonymous]

Hi

Requirement is finding top and bottom leaves taken by employee in a company ,

->  In slicer widget add Top 10, Top 20 and second slicer bottom 10 , bottom 20

-> In combo chart widget display the employee leaves list top to bottom and bottom to top based on slicer widget.

Above  Top N rows Query is wroking properly where as second Bottom N query is not working

slicer                                        Bottom N leaves taken by employee

Bottom 10                               A  1

Bottom 20                              B    1

Bottom 30                              C    2

                                              D    4

When we click the bottom 10 slicer, display bottom 10 records

Thanks

kunuthus

[v-jianhe-msft]

Hi,

If you would like to get some advice on whether your DAX formula is right or not, you could share the report or the tables you are using. Because there may be other conditions or requirements in your original report.

However, if you just want to find top and bottom N rows in a table, you can use topN function.

For example:

I have a table

If I want to rank by sales,

For the top10, the expression is : Table TOP 10 = TOPN(10,Table9,Table9[Sales])

For the bottom10, the expression is : Table bottom 10 = TOPN(10,Table9,Table9[Sales],ASC)

BR,

Henry