5 Replies Latest reply on Feb 2, 2017 1:37 AM by Avia Mak

# Rank across complete data set

Hi All,

Need assistance in terms to develop a pivot table with Rank function:

Requirement:

We have a map where we are plotting Rank City wise based on Score.

If a particular user click on a specific city, a pivot table will be shown with the rank of the selected city in comparision to the rest of cities

Data is as follows:

 Year City Level1 Level2 Level3 Level4 Score 2015 ABC Enb PE & SE PE Q1 84.62 2015 ABC Enb PE & SE PE Q2 90.35 2015 ABC Enb PE & SE PE Q3 80.00 2015 ABC Enb PE & SE EG Q4 58.82 2015 ABC Enb PE & SE EG Q5 61.21 2015 ABC Enb PE & SE EG Q6 83.07 2015 PQR Enb PE & SE PE Q1 34.62 2015 PQR Enb PE & SE PE Q2 35.74 2015 PQR Enb PE & SE PE Q3 90.00 2015 PQR Enb PE & SE EG Q4 60.00 2015 PQR Enb PE & SE EG Q5 80.00 2015 PQR Enb PE & SE EG Q6 65.00

Complete Pivot table output:

 City Level1 Level2 Level3 Level4 Score Rank ABC Enb 76.34 1 PE & SE 76.34 1 PE 84.99 1 Q1 84.62 1 Q2 90.35 1 Q3 80.00 2 EG 67.70 2 Q4 58.82 2 Q5 61.21 2 Q6 83.07 2 PQR Enb 60.89 1 PE & SE 60.89 2 PE 53.45 2 Q1 34.62 2 Q2 35.74 2 Q3 90.00 1 EG 68.33 1 Q4 60.00 1 Q5 80.00 1 Q6 65.00 1

Final pivot table would show only 1 city at a time i.e based upon the user selection in the map Eg.if City 'ABC' selected follwoing would be the output:

 City Level1 Level2 Level3 Level4 Score Rank ABC Enb 76.34 1 PE & SE 76.34 1 PE 84.99 1 Q1 84.62 1 Q2 90.35 1 Q3 80.00 2 EG 67.70 2 Q4 58.82 2 Q5 61.21 2 Q6 83.07 2

I hope I was clear in explaining my query.

• ###### 1. Re: Rank across complete data set

Hello Avia,

I created a example workbook based on your descriptions except for "rank" field.

What's the logic of this calculation?

Regards

• ###### 2. Re: Rank across complete data set

Dear Lei,

We are calculating Rank of each city across various level.

For example:

Level 3 EG for City ABC is Rank 2 as the Avg Score is 67.70

Level 3 EG for City PQR is Rank 1 as the Avg Score is 68.33

I hope that clarifies.

• ###### 3. Re: Rank across complete data set

Hello AVIA,

I added a table calculation for rank, but get three values different to your screenshot.

For example, my rank is,

Level 3 EG Level 4 Q6 for City ABC is ranked with 1 as the Score is 83.07

Level 3 EG Level 4 Q6 for City PQR is ranked with 2 as the Score is 65.00

which is opposite to the screenshot.

With the introduce of table calculation rank, it becomes impossible to use "use as filter" function in the dashboard.

Please check out the attached workbook.

Regards

• ###### 4. Re: Rank across complete data set

Dear Lei,

I guess the problem is solved using your solution, let me try it on the production data.

Regards,

• ###### 5. Re: Rank across complete data set

Dear Lei,