Forum Discussion
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:
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!
Hi waltmiller
You can refer to the follwing solution.
Sample data
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
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:
Any ideas? Is it a data type issue in my source data?
6 Replies
Hi waltmiller
You can refer to the follwing solution.
Sample data
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
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:
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
Hi,
Share the download link of the PBI file and show the expected result very clearly.
