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.
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!
Table of Contents
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.
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.
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.
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.
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.
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.
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.
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.
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.