You have calculated field created incorrectly.
just edit that field and click on "Default Table Calculation" at bottom right.
In compute using , you have selected Automatic. Change it to "Pivot Field Values (Groups)" and save it
it should work perfect.
Let me know if you are facing issue and need help.
I don't have a calculation for sheet 3. The calculation1 is for sheet 2. I don't know how to the do the calculation and ranking. Unless I'm misunderstanding what you are looking at?
Awesome! Thank you!
happy to help