Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
Easy question i hope, if i have table where i have displayed some values and rows of data (without summarizing it), is there a way to add a column that shows the number of rows displayed based on the way i sort the data. So for example:
Country | Start Date | End Date | Price Discount (Y/N) | On-Site/Off-Site | Cost (EUR) | Rank |
United Kingdom | 12/2/2019 | 5/3/2019 | No | On-Site | € 100,000 | 1 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 95,000 | 2 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 90,000 | 3 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 85,000 | 4 |
United Kingdom | 12/2/2019 | 5/3/2019 | No | On-Site | € 80,000 | 5 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 75,000 | 6 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 70,000 | 7 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 65,000 | 8 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 60,000 | 9 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 55,000 | 10 |
United States of America | 2/1/2019 | 2/14/2019 | No | On-Site | € 50,000 | 11 |
China | 1/1/2019 | 1/31/2019 | Yes | On-Site | € 45,000 | 12 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 40,000 | 13 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 35,000 | 14 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 30,000 | 15 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 25,000 | 16 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 20,000 | 17 |
China | 8/21/2019 | 9/1/2019 | Yes | On-Site | € 15,000 | 18 |
China | 1/1/2019 | 1/31/2019 | Yes | Off-Site | € 10,000 | 19 |
China | 1/1/2019 | 1/31/2019 | Yes | Off-Site | € 5,000 | 20 |
This table is sorted by the spend and the rank is displayed accordingly. I would like to add another column say sales, and if i sorted by that column the rank column to update for that sales column. Is this possible?
Solved! Go to Solution.
Hi @natabird3 ,
Sorting multiple columns using the RANK () function?
Like this?
//column
Rank =
RANKX(
FILTER(
Sheet7,
Sheet7[Country] = EARLIER(Sheet7[Country])
),
Sheet7[Cost (EUR).2],
, ASC, Dense
)
Or like this?
//Column
Rank(1) =
RANKX(
Sheet7,
[Sum(cost)],
,ASC,Dense
)
If these are not correct, Please give us an output table (made in Excel) and indicate whether each column is roriginal data or comes from a DAX formula?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @natabird3 ,
Your formula for [Rank] column should look like this:
Sales_Rank= RANKX(ALL(test[name]),[Spend],,DESC,Dense)
If you want to sort the Sales column, the formula should look like this:
Sales_Rank= RANKX(ALL(test[name]),[sales],,DESC,Dense)
You can find that RANKX() function only sorts a certain column, and it can't achieve dynamic changes.
But you can implement multi-column sorting:
Sorting a table using multiple columns
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
can you please try the below measure to get
Sales_Rank= RANKX(ALL(test[name]),[sales],,DESC,Dense)
Proud to be a Super User! | |
Hello,
Thanks for the reply. Unforutantely, i have already tried a few of these rankx options but the desired result is not coming. Jut so i understand the "test name" is the dimension that i am trying to use, so like country for instance, and the [sales] is the value that i want to rank, which is a measure say sum of sales. I put all that in a measure and all i get is 1 and 2 for most values but not an accuarate ranking at all. Any idea why is that happening? Thanks in advance for the help.
Hi @natabird3 ,
Sorting multiple columns using the RANK () function?
Like this?
//column
Rank =
RANKX(
FILTER(
Sheet7,
Sheet7[Country] = EARLIER(Sheet7[Country])
),
Sheet7[Cost (EUR).2],
, ASC, Dense
)
Or like this?
//Column
Rank(1) =
RANKX(
Sheet7,
[Sum(cost)],
,ASC,Dense
)
If these are not correct, Please give us an output table (made in Excel) and indicate whether each column is roriginal data or comes from a DAX formula?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
72 | |
56 | |
38 | |
35 |
User | Count |
---|---|
83 | |
68 | |
59 | |
46 | |
45 |