How to Sum, Subtotal, and Grand Total in Alteryx

Alteryx allows you to sum multiple columns of numbers together using a Formula tool or calculator subtotals for an entire column by using a Summarize Tool. There are also methods that involve using a crosstab to auto-calculate row and column totals that come in handy.

Calculating sums and totals in Alteryx largely depends on the shape of incoming data, while it's easy to add two columns together it's a multi-step process to add subtotals

Performing various calculations can be challenging in Alteryx. Rather than performing an operating by pointing to a cell number or range, you have to perform calculations at the full column level. One of the implications of this is performing calculations as a separate step then adding the results back into your table of results. We’ll explain.

Let’s roll!

Adding Two or More Columns in Alteryx

To add multiple values together in Alteryx, add a Formula Tool from the Preparation section into your workflow. Choose to Add a New Column or Update an Existing Column by selecting a column name. Then use the formula: [Column1] + [Column2] the results will be the sum of two columns.

To avoid errors, both columns must start with a numeric data type before going into the Formula Tool.

In the example below, we created a new “Gross Margin” column that adds Sales and Cost columns together. The column names are surrounded with [Square Brackets] to designate the column name. The formula tool also defines the new column as a double (numeric) data type.

Example of an Alteryx Sum formula to add values from two columns together

Calculations are applied to the full column length at one time. There is no need to drag the formula down like you would in Excel and it automatically adjusts when rows are added or deleted if the input data changes over time.

Subtotaling a Column in Alteryx

Calculating a Subtotal in Alteryx involves two steps. First you have to use a Summarize Tool from the Transform section of the Alteryx Toolbar to calculate the sum a columns or columns. Second, use a Union Tool from the Join section to append the new Totals to the bottom of each column.

Start by adding and configuring a Summarize Tool to your workflow. The Summarize Tool is available in the orange Transform section of the toolbar.

An Alteryx Summarize Tool added to a workflow

The Summarize Tool is configured by adding columns from the top section of the configuration panel to the bottom section of the configuration panel with the “Add” button in the middle.

The add button allows you to choose an aggregation method, in the case we want to Sum. If Sum is greyed out or not available, make sure the incoming data types are numeric. You can only add numbers, and the option will be unavailable for string (text) data types.

How to configure a Summarize tool to group and calculate the total of a column

When you choose to Sum values in the Summarize tool, it will automatically re-name the columns with a Sum_ prefix. Delete the prefix, we want to make sure that our column names match the original column names. This way when we Union them in the next step they will automatically align as expected.

Renaming values in a summarize tool

The results of the Summarize Tool will be the grand totals of the data coming in. You can add additional grouping levels in the Summarize tool if you want to Subtotal by Category. Our results below are for column totals that do not contain any additional grouping.

Example results of a summarize tool providing column totals

Add a Union Tool which is typically used to append data sets together. In this case, connect the detail to the Union Tool First, which will be noted by a #1 connection. Then connect the Summarize tool noted by a #2 connection. When you run the workflow the totals will be added to the bottom of a data set.

Using a Union Tool to Append Totals to the bottom of line item detail

You may need to get creative when working with Totals in this way. Normally the order will be in place based on how the Union Tool is configured. You can use a Formula Tool to add a “Description” for the Total line by surrounding the text in quotes, and a Select Tool to re-order the columns.

Using a Formula and Select tool along with Summarize and Union to Label Totals and set the columns in order

When creating Group Subtotals, you may have to adjust the Descriptions or use a separate file to help set the line item order as it can be difficult to get subtotals to fit below each group with a grand total.

There are also times that you can create a sorting column that’s alphabetical, for example labeling a Grand Total something like Z-Grand Total then utilize a Sort Tool to sort a column alphabetically. In most data sets the Z column will be at the bottom of your data. Then you can use a Formula Tool to Replace “Z-” from the column prior to sending the workflow to an output.

If you’re final output is an Excel workbook, there are times where it may be easier to prep the data in Alteryx and output it to Excel before finalizing all of the formatting or adding Subtotal Formulas in Excel.

Calculating Row and Column Totals with Cross Tab

Within the Cross Tab Tool there are options to calculate Total Column and Total Rows automatically. The method may not be suitable for all data-sets because it involves applying a pivot style function to your data which transforms columns to rows. To enable Total Column or Total Row options, scroll down in the list of “Methods for Aggregating Values” section of the Cross Tab Tool and select them.

Using a Cross Tab tool to calculate row and column totals automatically when pivoting the data

The Cross Tab Tool allows you to select multiple aggregation methods at a time as well as grouping columns that will appear at the left side of the output results. When the data transformation fits the needs of your workflow, it’s a quick way to add column and row totals like you would in an Excel Pivot Table.

The Importance of Data Types

When you Sum or Summarize data to calculate the total of two columns, subtotals, or grand totals it’s important that the data starts as a numeric data type. Without the correct incoming data type select, the options to Sum or Total won’t be available. It’s a common reason why an option is greyed out and unelectable.

Conclusion

Adding multiple columns together to calculate a SUM can be quickly performed with a Formula tool when the incoming columns are defined with a numeric data type. Grand Totals and Sub-totals can be calculated with a Summarize Tool, but it’s a two step process. You have to first calculate the value then append the values to the bottom of your line item detail using a Union Tool.

The Cross Tab tool allows you to Pivot data while calculating row and column totals. This can be a quick and efficient way to add totals, but may not fit all data set use cases.

Setting the order of subtotals and grand totals can be tricky and requires some creativity. Formula tools can add specific text line descriptions, and the sort tool can order them. However, at times a custom order may be needed where you can Join another table that lists the rows in the order that you need them.

Scroll to Top