You can leverage sets to dynamically display top, 2nd top and remaining. In the attached I created a top 35 set, then a top 70 set and then a combined set of these two which gives the top 70 without the top 35 effectively filtering for next top 35. The remaining can simply be shown as out of top 70.
Hope this helps.
Split Into 3 Pages Example VP.twbx 129.3 KB
Thanks for the suggestion of using sets for this problem. I had not thought of that. The solution worked great with the simplified report mockup I attached, but I was having some problems with it working with the actual more complex report. I created a more realistic mockup and am playing around with it a bit. I'll let you know soon what I find.
There were two adjustments I had to make so that this solution would work in my more complex production workbook.
In the production workbook, I have a filter called Community that allows for the selection of one community out of many that are in the database. The problem I was running into was that the top 35 filter was selecting the top 35 question scores across all communities and then showing within that set the question scores that applied to the selected community. So the top 35 filter might only show 10 question scores that were both top 35 within the entire database and also belonged to the selected community. I was able to fix this by right clicking on the Community filter and selecting "Add to Context". This made it so that Top 35 was calculated after the Community filter was performed, and now works correctly in the production workbook.
The other issue I was trying to solve was how to make sure the Score 1 worksheets were sorted the same way as the Score 2 worksheets. I was concerned that tied question scores might not sort in the same order for both worksheets. For example, if 3 questions had the same score, would they sort in the same order in both worksheets. I created a custom field composed of the combined question score and the question text to force the sort to be the same for both worksheets, but Tableau was not sorting this field appropriately. So I removed the custom sort field and just sorted by Score 1. It looks like this will work for me, because it seems that Tableau defaults to sorting in data source order when the scores are tied, so the sorting should be consistent across worksheets.
Your solution is very helpful. Thanks Vasil.
The specifications for the problem you helped with me before changed a bit, so I thought I would see if you have a solution to that problem as well. Previously you helped me split a long sorted page into three separate worksheets that would each fit on a printed page. You utilized sets as filters to achieve this.
The tweak I need is to add another sorting level, and then filter the rows so that the first 35 appear on the first worksheet, the 2nd 35 appear on the next worksheet, and the rest of the rows appear on the last worksheet. FYI - Both sort levels are using the AVG(Score), just aggregated at different levels. The range of these averages will always between 0 and 100 inclusive.
I can submit this as a new problem if that would be helpful. I have attached a new workbook showing the new sorting level on the worksheet "All with New Sort Level".