Suggestion: Start a new sheet. Create a new set (highlighting dimensions Origin City and Destination City and right-clicking to "Create Set..." Create a Calculated Field called Rank with formula: Index(). Drag Rank to the filter shelf and limit the results to 1 through 10.
Note: This will only work if the set is sorted descending by the trip count. I noticed that you used Count() to aggregate. Might you instead want to use Count Distinct to get a count of distinct TripIDs?
Thanks David. The suggestion to create and filter by a new calculated field using the formula, Index(), gave me exactly what I wanted. As for your other suggestion, what is the advantage in a switch to a Count Distinct? Do I risk counting the same trip twice with just a Count?
Yep. If your data looks like this, for example:
TripID Leg Minutes
1 a 90
1 b 60
2 a 15
If you do a Count(TripID), you'll get 3 because it simply counts the rows. If you do a CountD(TripID) you'll get 2 because it counts the unique values in the TripID column.
Thanks for the advice. The last thing I want to do is inflate my numbers.
If it is not too late for a follow up question, I have created a new table with the same issue as before but where the Rank calculation field is not working. Would you have time to take a quick look?
Thanks --- William
I'll give it a shot. Can you attach or publish the workbook so I can dig in?
1 of 1 people found this helpful
Found it! By default, the Rank was being computed by "Table (across)" when you want it to compute by "Table (down)." If you would have swapped rows and columns, it would have worked (you'd have seen the top 10 tripIDs across the columns). But that's hardly what you want to see.
So, just drag the Rank field onto the Filters shelf, click the pill's dropdown, change the Compute Using value to Table (Down), and specify the range 1 to 10.
And remember that you probably want to use CountD instead of Count here, too.
Awesome. Thanks for the second round of help with my "Top 10" needs. Now that I understand how to use the "Rank" calculation field, I plan to make frequent use of it.
Thanks --- William