Doesn't this look right?
What you needed to do was correct the formula for the overall rank, and also specify how the ranking is to be computed for each of the individual ranks being referred in the overall formula.
After you specify how the rank is to be computed for the Overall Rank, you should click on the triangle beside the "Overall Rank" text. You will then see the options for selecting how the rank should be computed for the Rank Frequency A, B and Patients as seeing in the 2nd image below. You can then set the ranks for each of the individual items as shown below for the Rank Frequency A.
Hope this helps. Updated workbook is attached.
Edited 2/4: Sorry, I didn't mention that because the data is getting filtered out, your rank will not work in the tooltip and shows up all 1. So, I had modified the tooltip to display all the values. And if you do not want the nulls to also show up as green color, you may want to use discrete values and assign colors. I have now assigned the white color to the null value.
Sample Workbook.twbx 91.9 KB
Not certain I fully understand the goal here
You can create a ranking based on the total but I think yo want to hover over a value and see in the tool tip a filtered version of another sheet - i.e only see Bob but also see Bob's rank?
Unfortunately that is not going work - Rank (in all its form) is a Table calculation that happens last in the order of operations - after all filtering has taken place
The table calculation operates on the underlying table unique to each sheet - so your main sheet that includes all the people and Rank can use all the dat to rank each person
when you pass the person's name to the second sheet - that sheet is filtered by the name leaving only a single person in the filtered table - so Rank returns 1
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.