Forum Discussion

waltmiller's avatar
waltmiller
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    waltmiller
    2 years ago

    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?

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    waltmiller
    Frequent Visitor
    2 years ago

    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?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi waltmiller 

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

       

      Best Regards!

      Yolo Zhu

      • waltmiller's avatar
        waltmiller
        Frequent Visitor
        2 years ago

        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!