Rank a Ranked Measure

Hi,

I have a dataset with 100 Stores/Branches and each store has a 25 KPI scores.   If I rank the branch by KPI Score, then it's based on the overall score.  What I need to do is rank each KPI for that branch, sum that rank, and then rank those by the lowest.

So, currently if one branch has a really high KPI score of 1000 then they are always top in the rankings.  They may first in that one KPI and last in the other 24 KPI's, so overall they're rank should be nearer the bottom.

See below an example of 3 Stores/Branches and 3 KPI's  In the sum (shown in yellow) shows Branch A as Rank 1.  If you rank each KPI, sum those ranks (shown in green) and then rank those by the lowest position, then Branch B  is the highest rank, as it's ranked positions are 2,1,1 and branch A was 1,4,4 

Any help with this would be much appreicated.

Lee.