Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
waltmiller
Frequent Visitor

Rank/RankX For Multiple Columns With Repeating Values

Hi helpful folks,

I have a table with the following important columns: Organization, Department, Funding, Year. Currently working with the previous 5 years of data, but future data will be ingested in addition to backfilling to 2006. 

 

I need a report visual (or two visuals, one each for Org and Department, is also okay) where a user can filter through the most funded organizations and departments by year. 

 

Sample Table:

 

waltmiller_0-1710889318444.png

This format but there are 150+ Organizations, and each has 10-18 of the 18 total departments, with a row for each year of each ORG + DEPT iteration. I've tried a variety of RankX based measures and still can't quite get the Org rankings right, let alone including the departments.

 

I really appreciate any potential solutions and explanations offered to a new user shifting from Excel and SQL. 

 

Thank you!

2 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

Hi @waltmiller 

You can refer to the follwing solution.

Sample data 

vxinruzhumsft_0-1710899193847.png

For the requirement you have offered, you can create two measures.

Rank_org = RANKX(ALLSELECTED('Table'[Org]),CALCULATE(SUM('Table'[Funding])),,DESC,Dense)
Rank_dept = RANKX(ALLSELECTED('Table'[Dept]),CALCULATE(SUM('Table'[Funding])),,DESC,Dense)

Output

vxinruzhumsft_1-1710899331808.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

Thank you, Yolo Zhu. That's close to what I've been trying, but every time I do this, all ranks are returned as 1:

waltmiller_1-1710902405085.png

 

Any ideas? Is it a data type issue in my source data?

 

 

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file and show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xinruzhu-msft
Community Support
Community Support

Hi @waltmiller 

You can refer to the follwing solution.

Sample data 

vxinruzhumsft_0-1710899193847.png

For the requirement you have offered, you can create two measures.

Rank_org = RANKX(ALLSELECTED('Table'[Org]),CALCULATE(SUM('Table'[Funding])),,DESC,Dense)
Rank_dept = RANKX(ALLSELECTED('Table'[Dept]),CALCULATE(SUM('Table'[Funding])),,DESC,Dense)

Output

vxinruzhumsft_1-1710899331808.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thank you, Yolo Zhu. That's close to what I've been trying, but every time I do this, all ranks are returned as 1:

waltmiller_1-1710902405085.png

 

Any ideas? Is it a data type issue in my source data?

 

 

Hi @waltmiller 

Can you provide the picture that the field you put into the table visual?

 

Best Regards!

Yolo Zhu

That was it! I didn't realize I had copied a data table and used the wrong reference. I'll accept your first post as a solution. Thank you very much!

Hi @waltmiller 

If my solution helps, please consider it as a solution.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors