Totaling grouped data

One of the primary purposes for breaking data into groups is to find a total for each group of records instead of for all records in the report.

When the program totals data, it sorts the data, breaks it into groups, and then totals the values in each group.

Many totaling options are available. Depending on the data type of the object you plan to total, you can do the following:

● Sum the values in each group.

● Count all of the values or only those values that are distinct from one another.

● Determine the maximum, minimum, average, or Nth largest value.

● Calculate up to two kinds of standard deviations and variances.

For example:

Option Description

Customer list reports Determines the number of customers in each state. The total would count the distinct customers in each state group.

Purchase order reports Determines the average order placed each month. The total would calculate the size of the average order for each month group.

Sales reports Determines the total sales per sales representative. The total

would sum or subtotal the purchase order amounts for each sales representative group.

You can also add a subtotal to your report. A subtotal is used to total part of a series of numbers. For example, you may want to create a report that provides a total of last year's sales for each country in addition to the grand total of last year's sales for all countries.

8.3.1 To total grouped data

Before you can total grouped data, you must create a report with grouped data.

1. On the Insert tab, click Total.

2. Select options for your total:

Option Description

Total operation The mathematical operation to perform on the grouped data, such as Count of, Average of, or Maximum.

Note

If you are totaling a delegated measure, select Delegated as the operation.

Totaled object The object of data to be counted.

Placement Whether to place the total in a custom location, at all group levels, in the report footer, or for each grouping of <<object>>.

Location Whether to place the total above data or below data.

3. Click Insert.

4. If you want to change any of these settings, right-click the total and then click Format Total.

The Format dialog box appears, and you can edit a variety of options.

Related Links

Calculating Percentages [page 172]

Grouping data hierarchically [page 166]

8.3.2 Ordering groups by totaled values

You can organize groups by total value in either ascending or descending order. For example, in a purchase order report, if you subtotal the order amount by state, you can order the groups:

● From lowest to highest order amount (ascending).

● From highest to lowest order amount (descending).

8.3.2.1 To order groups by total value

Create a report with totaled data.

1. Under the Data tab, click Sorts.

The Groups and Sorts dialog box appears.

2. On the Sorts tab, expand the options for the group by clicking the arrow next to the group name.

3. Click Add Sort tab to select which element to sort the report.

A sort based on the total is automatically added to the group.

4. Using the arrow next to the Sort By object, select the sort order: Ascending or Descending.

5. Click OK.

The records in the group are ordered based on the total value(s) specified.

8.3.3 Selecting top or bottom N groups or percentages

At times, you might want to show only the top or bottom groups, or percentage values in a report. For example, you might want to show only the fastest selling product lines, the countries that account for the top 25 percent of sales, the states that generate the most orders, and so on.

With top N groups or percentages, you are instructing the program to display the groups or percentages that have the highest summary values (Top N).

With bottom N groups or percentages, you are instructing the program to display the groups or percentages that have the lowest summary values (Bottom N).

When setting up top or bottom N, you also have to consider what to do with the records that do not fit your top or bottom N criteria. You can either eliminate these records from your report, or collect them into a single group.

Note

In a top or bottom N selection, it is recommended that you do not use a group that is sorted hierarchically. The integrity of your hierarchical group sort might be affected by the top or bottom N selection.

Related Links

Totaling grouped data [page 169]

8.3.3.1 To select top or bottom N groups

Before you can select top or bottom N groups, you must create a report with totaled data.

This procedure shows how to select top or bottom N groups. Top or bottom N percentages work the same as top or bottom N groups, except that you define the percentage value instead of the number of groups.

1. On the Data toolbar, click Sorts.

The Groups and Sorts dialog box appears.

2. On the Sorts tab, select Top N or Bottom N from the Show list for the group.

3. In the N is box, enter a value for N.

4. Select Include Ties to accommodate groups whose totaled values are equal.

For example, suppose you had the following groups:

○ Order 1 = 100

○ Order 2 = 90

○ Order 3 = 80

○ Order 4 = 80

If you set your top N to be three, but you do not select Include Ties, your report will show Order 1, Order 2, and Order 3.

However, if you select Include Ties, your report will show Order 1, Order 2, Order 3, and Order 4 even though N is set as three. In this way, the program accommodates the equal values of orders 3 and 4.

When the program runs the report, it will include only the groups that you specified.

5. Select the Include Others, with the name check box to display other records, or clear this check box to hide the other records.

If you select this option, enter a name for the other records to appear under.

6. Click OK.