Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to add ranking column in my pivot table. I used rank formulas in calculated field but it just #N/A. How to calculate ranking based on the value in pivot table?
Solved by E. W. in 44 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 30/06/2018 - 04:09
Greetings!
Excelchat Expert 30/06/2018 - 04:10
Thank you for choosing Got It Pro.
Excelchat Expert 30/06/2018 - 04:10
You need help on Pivot Table rankings, correct?
User 30/06/2018 - 04:10
yes
Excelchat Expert 30/06/2018 - 04:10
Alright. Do you have an example data that we could work on?
User 30/06/2018 - 04:10
I use spreadsheet and I don't know how to add ranking formulas
User 30/06/2018 - 04:10
yes I have
User 30/06/2018 - 04:11
I'll send you this link
Excelchat Expert 30/06/2018 - 04:11
Alright. Can you paste the data here in this open workbook we have?
Excelchat Expert 30/06/2018 - 04:11
Or just attach the file. You can do that by clicking the "Clip" icon beside the chat window
User 30/06/2018 - 04:11
I do it on spreadsheet
User 30/06/2018 - 04:11
Can I send you the link?
Excelchat Expert 30/06/2018 - 04:12
Alright, that should be fine.
User 30/06/2018 - 04:12
https://docs.google.com/spreadsheets/d/1WjD_IE-Rure2Qia8-ZpDXuh1FzwFJayaaYnDw77xvVo/edit?usp=sharing
User 30/06/2018 - 04:13
I wanna add ranking column in Sum of Revenue by each items - 5 months (All Restaurants) table
Excelchat Expert 30/06/2018 - 04:13
Okay, give me a minute.
Excelchat Expert 30/06/2018 - 04:13
I don't have access to it.
Excelchat Expert 30/06/2018 - 04:13
Can you please just save the file and upload it here?
User 30/06/2018 - 04:13
I have just gave you an access
User 30/06/2018 - 04:14
because all of my sheet link together
Excelchat Expert 30/06/2018 - 04:14
Alright
User 30/06/2018 - 04:14
Can you check again? Sorry for this
Excelchat Expert 30/06/2018 - 04:14
You want to add a ranking for each month?
User 30/06/2018 - 04:14
only for May
Excelchat Expert 30/06/2018 - 04:15
Okay, hold on.
User 30/06/2018 - 04:15
Thanks a lot
Excelchat Expert 30/06/2018 - 04:19
Alright, first, your sheet is kind of complicated. I need you to do me a favor.
Excelchat Expert 30/06/2018 - 04:19
Can you duplicate the data of the Sum of May values?
User 30/06/2018 - 04:20
behind May column?, give me 1 minutes
Excelchat Expert 30/06/2018 - 04:20
Alright.
User 30/06/2018 - 04:21
I did it
Excelchat Expert 30/06/2018 - 04:21
Okay, hold on.
User 30/06/2018 - 04:22
yes
User 30/06/2018 - 04:30
Ranking Dimension is just Rank column I did in Sheet All - R
User 30/06/2018 - 04:30
By using formulas (if you need more in formation ^^)
Excelchat Expert 30/06/2018 - 04:30
That is the ranking values we are expecting?
User 30/06/2018 - 04:31
yes
User 30/06/2018 - 04:31
But I can't display it in pivot table
User 30/06/2018 - 04:31
it's so wrong
Excelchat Expert 30/06/2018 - 04:31
I see, hold on.
User 30/06/2018 - 04:32
I wanna add ranking for 27 key category first, from 1-27
User 30/06/2018 - 04:32
and when you expand this pivot table, we can add ranking based on your cell you expand
User 30/06/2018 - 04:33
anyway, I need to rank 27 key category from 1-27 first. All - R sheet is focus on the detail only
Excelchat Expert 30/06/2018 - 04:33
Understood.
Excelchat Expert 30/06/2018 - 04:34
We're getting close. It is just the difference between Google Sheets and Microsoft Excel that I have to figure out.
User 30/06/2018 - 04:38
yup I hope so. I tried a lot for 2 days but I can't find any way to do. No filter setting, no value setting, just using formulas. It can't understand cell I indicate :(
Excelchat Expert 30/06/2018 - 04:39
Exactly, there's also a limitation on the options on how to show the values as. And also sorting...
Excelchat Expert 30/06/2018 - 04:39
But we're getting closer, hold on.
User 30/06/2018 - 04:40
Yes, thanks a lot
Excelchat Expert 30/06/2018 - 04:44
Is it okay if we sort them by it's ranking?
Excelchat Expert 30/06/2018 - 04:44
Or does it have to stay the same way?
User 30/06/2018 - 04:45
it's okay if it don't change my category group
Excelchat Expert 30/06/2018 - 04:46
You mean column A?
User 30/06/2018 - 04:46
yup
Excelchat Expert 30/06/2018 - 04:46
Oh okay
User 30/06/2018 - 04:46
the detail in each category we can change
User 30/06/2018 - 04:46
the number of this is ok too
User 30/06/2018 - 04:46
don't worry
User 30/06/2018 - 04:46
just don't change these name and item details in this
Excelchat Expert 30/06/2018 - 04:47
Got it
User 30/06/2018 - 04:47
But I still prefer to add one more column. I hope so.
Excelchat Expert 30/06/2018 - 04:48
Okay, it seems like we are really limited in the pivot table functionality of google sheets. It's unfortunate but we can't add the ranking within the pivot table.
Excelchat Expert 30/06/2018 - 04:49
What we can do is add another separate column.
User 30/06/2018 - 04:49
I got it. Just do all of things you can
User 30/06/2018 - 04:50
if we add separate column, we can use formulas like excel, right
Excelchat Expert 30/06/2018 - 04:50
Yes.
User 30/06/2018 - 04:51
I understand. And I know how it works. Thanks a lot for spending your time with me
User 30/06/2018 - 04:51
Thanks a lot
Excelchat Expert 30/06/2018 - 04:51
Alright. You could also try and change the Column Header for Column J that would make sense.
Excelchat Expert 30/06/2018 - 04:52
I'm happy to be of service. Please try and leave a feedback after you end our chat session. Please choose Got It Pro once again! Thank you!
User 30/06/2018 - 04:52
Yes. I'll do that. Hope you have a nice day
Excelchat Expert 30/06/2018 - 04:52
You too!
User 30/06/2018 - 04:53
Thanks ^^

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.