Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
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:
Regards,
Ahmed Raza
Hi,
May I know how is this issue going currently?
BR,
Henry
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |