Create Index calculation: Index()
For first sheet drag in filkter and select 1 to 8
for second select the others
I have removed my rowFilter.
Have added index calculated field in filter containing calculation: INDEX()
But it doesn't work right. I have selected index values in a range from 1 to 7 for Pichart1 worksheet. The screenshot is:
Similarly I selected values from 8 to 15 in next worksheet. The snapshot is:
It doesn't give right pie-charts.
INDEX() computes a value for each distinct address. With 15 cars and 1 or 2 values of the CCFC auto population dimension that's going to be 15+N values returned for INDEX(), the exact values being dependent on the addressing of the calculation. That's why some pies are repeating.
A better calculation would be RANK_DENSE(MIN([Car Name])) with a compute using on both the Car Name and CCFC auto population dimensions, that will return a number from 1-N for each Car Name regardless of how many CCFC auto population values there are for that car name.