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]
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]
