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.
I have a table recording as below.
Name Salary
Jake 1000$
Jame 1500$
Malee 2500$
Tony 2000$
Stark 5500$
Jessy 4500$
Johny 6000$
Eron 5500$
Mark 8000$
Eric 4000$
Max 3000$
Min 3800$
Viola 4000$
Simon 5300$
Aka 2550$
Now, I have 15 people but I want to calculate salary for total of 10 people who highest (top 10) and also number them for ranking.
Can anyone helps me? Which DAX can I use?
I've already tried but it doesn't suceed now.
Solved! Go to Solution.
You Need to create 3 measure in order to get what you wanted
Salary Measure
Salary_m = SUM(Ranking[Salary])
Salary Rank
SalaryRank = RANKX(ALL(Ranking),[Salary_m],,DESC,Skip)
TotalSalaryTop10Rank
SalaryTop10 = CALCULATE([Salary_m],FILTER(Ranking,[SalaryRank]<=10))
Top 10 Highest Salaries =
VAR
Rankingcontext = Values(Table[Name])
Return
CALCULATE (SUM(Table[Sales]),
TOPN(10,
ALL(Table[Name]),
SUM(Table[Sales]),
Rankingcontext)
This should return your top 10 names by salary. What I would do is create a measure that represents the SUM(Table[Sales]) and insert it where the sum statements are located in the Dax formula.
Hey!
I realize I didn't include a solution for your need to list the number of the ranking. That said, my original solution will not work for you because you have to leave the salary value set to summarize.
You will need to enter two new measures--SUM and RANKX.
Sum Salary = SUM(Salary[Salary])
RankX AllSelected = RANKX(ALLSELECTED(Salary[Name]),[Sum Salary])
That leaves you with a table where all salaries are displayed:
There are probably several ways to get your top 10. Here is one way: Set the visual filter for your RANKX measure to display items that are less that 11:
That will get your table displaying the top 10 salaries:
Here are some YouTube vids that might help (published by Curbal):
RANKX: https://www.youtube.com/watch?v=z2qzJVeYhTY&t=698s
RANKX & TOPN: https://www.youtube.com/watch?v=SsZseKOgrWQ&t=4s
Here is a post in the PBI community that explains how to get around the issue where all items are ranked with 1:
http://community.powerbi.com/t5/Desktop/Rankx-all-return-1/m-p/48025#M18973
You Need to create 3 measure in order to get what you wanted
Salary Measure
Salary_m = SUM(Ranking[Salary])
Salary Rank
SalaryRank = RANKX(ALL(Ranking),[Salary_m],,DESC,Skip)
TotalSalaryTop10Rank
SalaryTop10 = CALCULATE([Salary_m],FILTER(Ranking,[SalaryRank]<=10))
Hey!
I was working on something very similar yesterday, and took the same approach in that I looked to solve it with DAX via either a column or measure, but DAX isn't necessary to solve your problem.
Simply add a table with names and salaries, and then adjust the filter for salary by setting the filter type to 'Top N'.
You can then change the 'Show items' value to 'Top' and '10.' Additionally, you will have to drag the salary field into the 'By value' field labeled 'Drag data fields here.'
You then have to change the sort option of the table by setting it to 'Sort By Salary.'
You aren't left with only 10 entries in the table though because you have two people with a salary of $5,500, and two people with a salary of $4,000.
Hope this helps.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |