How to Add Grand Totals to Your Data

Have you ever wondered how to add Subtotals and Grand totals to your data set? Here is one method;

In this example I start out with the table below in a Text Input Tool.

I add a Formula Tool to calculate the Qty * Cost as the Sales Amount.

I add my first Summarize Tool to calculate the subtotal for the Type and set it up as below;

I group by Type.

I grab the Sum of Qty, Cost and Sales Amount.

I add a second Summarize Tool to my canvas and connect it to the Formula Tool (separate from the other Summarize Tool) to grab the total of Qty, Cost and Sales Amount. I manually delete the Sum_ in the Output Field Name column. (As shown below;)

Next I add a Formula Tool to the first Summarize Tool and add the text "Subtotal" to the Type field.

Next I add a Dynamic Rename Tool to remove the prefix Sum_ to all the headers of the first Summarize Tool results. This is after the Formula Tool in the workflow.

Please note: You could just delete the Sum_ of prefix in your Summarize Tool, but I wanted to display that this other tool can also be used to perform this function.

I select Remove Prefix/Suffix from the Rename Mode drop-down and select the Sum fields. I then enter "Sum_" in the remove field and select Prefix.

Next I add a Union Tool and attach it to the Dynamic Rename Tool. I drag a connector from the first Formula Tool to this Tool. As shown below;

Next I use a Sort Tool and select Ascending for Type and then Qty.

I add another Union Tool to connect this data stream and the second Summarize Tool together; however, I need to check the Set a Specific Output Order box at the bottom of the Configuration pane for this Union Tool. This places the grand total row at the bottom of the data.

As you see in the results below, the Grand Total summary from the second Summarize Tool is on the bottom with a Null for Type.

I add another Formula Tool after this Union Tool with the following formula for the Type column;

IIF(IsNull([Type]),"Grand Total", [Type])

I add a Select Tool after the Formula Tool to move the Produce column be after the Type column.

Next I add a Basic Table Tool.

I click on Cost, and Sales Amount and adjust the decimal places to 2.

Then I click on the Edit Row Rules button on the bottom of this Configuration pane.

I create a Row Rule 1 with the following formula to apply special text to any row containing the word Subtotal;

Contains([Type],"Subtotal")

I change the Font to Arial, change the Font Size to 9 and click the Bold button.

I create a second Rule with the following formula;

Contains([Type],"Grand")

I change the Font to Arial, change the Font Size to 10, click the Bold button, and then select Silver for the Background Color.

Both Rules are shown Below;

I click the Close button when I am completed with these changes.

Now when I add a Browse Tool after my Table Tool, the results appear as below;

I can then export these results as needed.

My final workflow should appear as below;

This tip was a combination of responses from this article in the Community Alteryx website.