Forum Discussion

SabineOussi's avatar
SabineOussi
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

Raking within a group

Hello,   I am trying to represent sections averages in different schools from the highest section average to the lowest within each school. Below is an example, each school is represented by a col...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi SabineOussi,

 

You can use below formula to calculate the each rank of school, then use a clstered column chart to show the result.

 

Source table:

 

 

Calculate column:

 

Ranking = RANKX(FILTER(Sheet2,Sheet2[School]=EARLIER(Sheet2[School])),[Average],[Average],DESC,Dense)

 

 

Visual:

 

 

Regards,

Xiaoxin Sheng

SabineOussi's avatar
SabineOussi
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

Thank you Anonymous

 

Unfortunately I am connected to a complex database and there are a lot of selections through slicers in order to come with this particular graph.

So your solution is giving me the overall rank of the sections and not on that particular selection, so it's not really by school.

 

What I did is convert the schools to a high value number and add that number to the raking column and have my chart sorted by that.

School_Ranking: School1 = 100000, School2 = 200000
Final_Ranking: School_Ranking + Ranking

And finally sections are ranked correctly according to their schools.

 

 

One thing remains is to re-rank these sections from 1 to n instead of that big number under each bar.

 

Any thoughts?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi SabineOussi,

     

    Perhaps you can try to calculate the new rank based on the filtered columns.

     

    Regards,

    Xiaoxin Sheng