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.
How do we show top n values per category?
Say I want to show top 2 highest paid employees in each department as below then how can we do it in Power BI?
Department | Employee | Salary |
HR | H1 | 9k |
HR | H2 | 8.9k |
Finance | F1 | 10k |
Finance | F2 | 9.7k |
IT | I1 | 10.5k |
IT | I2 | 10.3k |
In SQL we can get this easily by using Dense_Rank function in CTE, but in power BI can someone please help me out?
@lordmukund , You can create a rank or topn column like
rank = RANKX(filter(allselected(table1,table1[Department],Table1[Employee]), Table1[Department] =max(Table1[Department])), sum(table1[Salary]),, desc)
In case Rank filter 2
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Hi Amit i tried this but it is not working.
I guess we need to use topn/groupby also along with this. but i am not sure how.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |