Working with Grouping Tables

Grouping Table Concepts

Columns, not Cells

If you're familiar with traditional spreadsheet tools, such as Excel, you likely associated data and formulas with individual cells. While Sigma tables are very spreadsheet-like, data is managed at the column level rather than on individual cells.  This means actions such as calculations and formatting changes are applied to every cell in a column.

Managing data at the column level ensures consistency and accuracy, and prevents common errors, across large and ever-growing sets of data.

Groups and Groupings

Groups and groupings, which are unique to tables, are an excellent tool for comparative analytics. They allow you to analyze rows of data based on shared values within a single column.

Any column in a table can be used to define a grouping. When used to define a grouping, the column becomes what we call a grouping key. Its individual rows merge into single cells based on their distinct values. Subsequently, the rows of data to the right of the key column in the table are bundled into groups based on each unique value in the key column.

Group aggregates can then be calculated based on the cell values within each row in the group.

Example: In the example below, we group sales data by [Store State]. We then calculate the total Sum of each row's [Line Item Sales] by creating a new calculation called [Sum of Line Item Sales]. Collapsing the table by grouping allows us to view this grouped and calculated data from a high level.

Add a Column & Create a Calculation

Add a New Column

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. In the element's editor panel, click the plus (+) button located to the left of the COLUMNS section header.
2. Select New column to create a new calculation or View source columns to select a column from the element's data source.
   • New column adds a new calculated column to the table and focuses the formula bar. Enter your calculation in the formula bar.
   • View source columns expands the full list of the element's available data source column. Check the box next to a column to add it to your table. 
     
     

Create a Calculation

The formula bar is central to calculating data in Sigma. It is located in the workbook toolbar at the top of any workbook page. Calculations are applied to entire columns. If a data element's column is selected, the column's formula will appear in the formula bar. No formula will be displayed if no columns selected.

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. Add a new column.
2. With the column selected, type your formula into the formula bar.
   It will suggest auto-completed function names and column names.
   
   
3. Once your formula is complete, hit Enter on your keyboard,
   or
   click the green checkmark to the right of the formula bar.
   
   
   

Create a Grouping

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. In the element's editor panel, drag and drop a column from the COLUMNS section onto the GROUPINGS section,
   or
   click the plus (+) button next to the GROUPINGS header and select a column.

Collapse and Expand Groups

Table groupings can also be expanded and collapsed by both columns and rows. To do this, click the plus (+) and minus (-) buttons on a grouping key's column header and cells.

Create Row Subtotals

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. Hover over the grouping key column, and click its caret (▼) button.
   This will open the column's menu.
2. Select Show totals.
   
   
   

Create a Summary

Summaries are single value column aggregates that are calculated at a table's highest aggregation level. 

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. Click the SUMMARY button, located in the table footer.
2. Click the section's plus (+) button.
3. Select a column option.
   • New summary creates a new blank summary column and focuses the formula bar. Type your custom formula to create you summary.
   • Row Count counts the total number of rows at the top most level of the table. If there are no groupings, this calculation will match the total number of table rows. If there are groupings, the calculation will match the total number of groups in the highest grouping.
   • Selecting any of the existing columns will create a summary aggregate of that column. Aggregation type (e.g. Sum vs Count) is dependent on the original column’s value type (e.g. text, number, date, etc).
     For example, a numeric column such as [Sales Amount] will create a new calculated column, [Sum of Sales Amount] (e.g. Sum([Sales Amount])).
     

Maximize the Table View

Column Formatting

Apply Basic Visual Formatting to a Column

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. Click to select the table.
2. Click the paint bucket () button in the workbook toolbar.
3. Select the column's formatting.
   

Apply Conditional Formatting

Before you start: This action uses the editor panel. If you have not done so already, open the editor panel from either Explore or Edit mode. Learn more

1. Click to select the table.
2. Click the paint bucket () button in the workbook toolbar.
3. Click Conditional formatting.
   
4. The table's conditional formatting options will appear in the editor panel. The selected column will automatically receive a default format.
   Define your new rule.
   
5. [optional] To add additional rules, click + Add rule.
   
   
   

Related Resources

Workbooks: An Overview
Intro to Data Elements
Working with Pivot Tables
Data Element Filters