Raking within a group

[SabineOussi]

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 color and each section is represented by a bar.

I tried that with a column chart and I can only sort by section (alphabetically) or by average (numerically).

I tried this ranking:
Rank = RANKX(VALUES([School Name]), RANK.EQ(1, [Final_average], ASC), ,ASC, Skip) 
but it didn't make any sense.

Any help on how can this be achieved in PBI?

Thanks.

[Anonymous]

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]

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]

Hi @SabineOussi,

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

Regards,

Xiaoxin Sheng