Totaling Numeric Data

When working with numeric information, you often need to see various summations of the data. Totals can sum rows and columns of numbers, find averages and standard deviation, compute subtotals and grand totals, and so on. When you add a Total to a worksheet, Discoverer automatically adds a column or row to the worksheet for the totals data.

In the example below, the Worksheet contains a sub-total for each Region and a grand total for all Regions.

Click to learn more about
Displaying Existing Totals
Creating a New Total
Editing a Totals Definition

See also:
Pivoting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Sorting Data
Calculating Percentages

Displaying Existing Totals

You can define totals for a worksheet and then display them on the worksheet or not.

To display totals or subtotals on a table or crosstab:

1. Open the worksheet to which you want to add a total.

2. From the menu, choose Tools | Totals. The Edit Worksheet dialog appears with the Totals tab selected. The list of totals shows all currently defined totals.

   
   

3. Click the box in front of the Total definition so that a checkmark appears.

4. Click OK. Discoverer now computes the totals and displays them in the table or crosstab.

   
   To remove the totals from the data:

1. From the menu, choose Tools | Totals. The Totals dialog appears.

2. Click the checkmark box(es) to remove the checkmark.

3. Click OK. Discoverer removes the totals from the table or crosstab.

Creating a New Total

Creating a new totals definition has three steps:

• Select the totals to calculate.

• Select the type of total and where to place it in the table or crosstab.

• Create a label for the totals column or row.

  
  To create a new totals definition:

1. Open the worksheet to which you want to add a total definition.

2. From the menu, choose Tools | Totals. The Edit Sheet dialog appears with the Totals tab selected.

3. Click the New button. The New Total dialog appears.

   
   

4. Click the data point drop-down list and select the data point to use for totaling data; for example, Profit SUM.

   You can also create totals for all the data points on the worksheet by selecting All Data Points from the drop-down list.

5. Click the drop-down list for the kind of total you want and select the calculation to use for totaling data.

The options are:

Sum--Adds all the values.

Average--Adds all the values and divides by the number of values.

Average Distinct--Adds all the unique values and divides by that number of values. Duplicated values are not included. For example, if a set of values includes 3, 3, 4, 5, 5, 6, and 7, the calculation of the distinct average is 3+4+5+6+7 divided by 5.The duplicate values of 3 and 5 are not included.

Count--Counts the total number of values.

Count Distinct--Counts the number of unique values.

Minimum--Finds the lowest value.

Maximum--Finds the highest value.

Standard Deviation--Calculates the standard deviation. Standard deviation is the square root of the variance of the values.

Standard Deviation Distinct--Calculates the standard deviation, but only using unique, unduplicated values.

Sum Distinct--Adds the values, but only using unique, unduplicated values. for example, the sum distinct of 3, 3, 4, 5 is 3+4+5=12. The duplicate value of 3 is not included.

Variance--Calculates the variance. Variance is the sum of the squares of the differences between each value and the arithmetic mean, all divided by the number of values.

Variance Distinct--Calculates the variance, but only using unique, unduplicated values.

Percentage of Grand--Calculates the Grand Total of the row or column, then finds the percentage of the current column or row of the Grand Total.

Percentage of Grand Distinct--Calculates the percentage of the Grand Total of the row or column, but only using unique, unduplicated values.

• Select where you want the total to be shown.

  Grand total at bottom--Calculates the Grand Total for a column and places it after the last row of the table or crosstab.

  Grand total on right (crosstab only)--Calculates the Grand Total for a row and displays it in a column on the right side of the crosstab.

  Subtotal at each change in--click the drop-down arrow to select the data item to use for the totals. For example, if you sort the data by Region, and want to see profits by region, select Region as the data item. Then, Discoverer automatically displays the total profit for each region on a separate line.

  All Group Sorted Items--displays totals for items set to be group sorted. For example, if the table contains two columns of numeric data set to be group sorted, subtotals are displayed for both columns. Data points not appropriate for the type of total are not displayed.

  For example, Region is a set of data points but summing Region by its data points doesn't make sense--it would be like trying to add "Central" to "East". In this case, Regions are not summed even if it is a group sorted item.

  A specific numeric data point (such as Profit SUM in the example)--displays totals for the selected set of data points.

  A non-numeric data point (such as Region in the example)--when you select a non-numeric set of data points, the options for the totals in the first drop-down list are limited to only those options that apply to non-numeric data points. For example, if you select Region, sum of regions does not make sense. The only totals that make sense for non-numeric data points are Count, Count Distinct, Maximum, and Minimum.

  Don't display subtotal for a single row--If the group of data consists of a single row, do not display a subtotal for it (the row's data value and subtotal are the same).

• Click one of the options for the current page or all the pages of the worksheet.

• Click the option to generate the label automatically if you want Discoverer to generate a label based on the data items being totaled.

  You can click the drop-down list for labels and choose additional options for the title from it. The options from the drop-down list produce labels that can change as the data changes by adding text codes (such as "&Item" and "&Value") to the label. In the actual labels in the table or crosstab, the ampersand (&) will not appear, and appropriate names from the table or crosstab will be inserted in place of the words "Item" or "Value".

  This table shows some examples.

  Option	Example	Sample label
  Insert Item Name	Latest Data from &Item	Latest Data from Region
  Insert Data Point Name	Top Performers of &Data	Top Performers of Profit Sum
  Insert Value	Total Income for &Value	Total Income for Central

  If the total calculates for all data points (as selected at the top of the dialog), the labels can appear for each appropriate name. For example, when totaling two items, and you select Insert Item Name (&Name), labels for both item names appear in the data or crosstab.

  To remove options from the labels, click in the label text in the dialog and edit it as you would regular text.

  9. Click OK when you're finished creating the Total.

  Editing a Totals Definition

  To edit a totals definition:

  1. Open the worksheet with the Total you want to edit.

  2. From the menu, choose Tools | Totals. The Edit Sheet dialog appears with the Totals tab selected.

  3. Select the definition you want to edit in the Totals dialog.

  4. Click the Edit button. The Total dialog appears.

  5. Make the changes you want.

  6. Click OK. The totals definition is now edited.