Ranking in a pivot table for all groups in a hierarchy

rar

New Member
Joined
Nov 29, 2005
Messages
8
Country State Zone Case Created Cases Closed
A 1 East 10 9
A 2 West 20 15
A 3 North 30 20
A 4 South 0 0
B 5 North East 50 10
B 5 North West 4 2
114 56


Here is the data table and the pivot with calculated fields is as under :


Country State Zone Closure% Score
A
1 90.00% 8

2 75.00% 4

3 66.67% 2

4 0.00% 0

A Total 73.33% 4
B
5
North East 20.00% 0
North West 50.00% 0
5 Total 22.22% 0

B Total 22.22% 0
Grand Total 49.12% 0


Is it possible to use RANK formula in pivot table itself to give ranks to each group in a hierarchy. In the above example I have the % for Zones, States and Country. What i need to do is to rank the Zones within a particular State, States within a particular Country and finally Country-wise. I mean to say in the initial pivot table i should have Country's ranking, when i drill down further, I should get the State wise ranking for each Country and on the next drill down, I should get the Zones ranking within each state.

Kindly help
[/img]
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

rar

New Member
Joined
Nov 29, 2005
Messages
8
NO SOLUTION FOR THIS PROBLEM !!!!!!!!!!!!!!!!!!!!!!!!
EXPERTS PLS HELP !!!!!!!!!!!!!!!!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,994
Members
412,633
Latest member
simon_elvin
Top