Rank/RankX For Multiple Columns With Repeating Values

[waltmiller]

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!

[v-xinruzhu-msft · Accepted solution]

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.

[Ashish_Mathur]

Hi,

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

[v-xinruzhu-msft · Accepted solution]

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.

[waltmiller · Accepted solution]

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?

[v-xinruzhu-msft]

Hi @waltmiller 

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

Best Regards!

Yolo Zhu

[waltmiller]

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!

[v-xinruzhu-msft]

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.