Sorting by Aggregates

To sort groups by the summary, or aggregate, calculation of each group of an Advanced Report, use Top/Bottom filters.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. Any arbitrary cell in the report can be the Top/Bottom filter.

To sort by a summary calculation:

1. Ensure that the report has the appropriate aggregate formula in a group footer or header (v2019.2+) cell. The cell should return a numeric value in the report output, which you want to sort the groups by.
2. Open the Filters dialog then, click the Top/Bottom tab. To open the dialog:
   • (v2021.1+) Click the Filters  icon on the toolbar
   • (pre-v2021.1) From the Settings menu, click Filters.
3. Check the Limit the report to the top/bottom values check box.
4. From the Top/Bottom list:
   • select Bottom to sort in ascending order, or
   • select Top to sort in descending order.
5. For versions v2018.2+: Check the All checkbox to sort all of the section values by the Top N criteria.
6. For versions pre-v2019.1: In the # field, enter 2147483647. Why this number? We cannot enter ∞, so instead we want to enter an arbitrarily large number. This is the largest number that can fit without causing a report error.
7. From the Value list, select the group footer or header (v2019.2+)cell with the aggregate formula.
8. If there is a For Each group, click the Delete icon to remove it.

Keep in mind that this is not a Sort from the sort menu. It cannot make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.