In Sigma, formulas written in columns give one result for each row of the grouping that the column is in …
… but what if you’re looking to calculate a single result based on an entire table of data, like a summary metric or topline KPI?
In this guide, we’ll go over how to use Summaries and Single Value Visualizations to not only calculate these single values, but easily reference them in other calculations throughout the rest of our workbook.
Summaries
Summaries are a special type of field on Workbook elements where formulas you write will give a single result that is based on every row of data in the table for that element.
In SQL terms, a Summary is an aggregate function applied to your entire table without a group by. In Sigma terms, a Summary is like a Calculation in a Group By that only has one row.
Add Summaries on Tables
For Table elements, you can create a Summary based on this data by clicking the ^ button next to the word “Summary” at the bottom left-hand corner of the element. This will show any existing Summaries on that element, as well as the + button that you can use to Add Summaries.
Summaries are formula-based calculations, so the options you select under Add Summary will either write a best-guess formula for you based on your selection or direct you to the formula bar to write your own formula if you select New Summary.
Add Summaries on Pivot Tables and Visualizations
For Pivot Table and Visualization elements, you can see the table for that element by pressing the Maximize Element button at the top right corner of the element … or by clicking the element and pressing the shortcut Spacebar.
This table is a component of the element, and is commonly referred to as the underlying table for that element. It will look and function much like a table element, expect that it’s groupings and calculations are determined by the columns you’ve used in the configuration fields on the left (Pivot Rows/Y-axis, etc.)
This underlying table also has a Summary menu, just like a Table element does!
From here, the steps for creating a Summary are the same as they are for a Table element!
Referencing Summaries
You can write references to Summary values in formulas in the same way that you can write references to other columns!
You can write a reference to a Summary in the same element with the format:
[<SummaryName>]
Where <SummaryName> is the name of the Summary
You can write references to a Summary in a different element with the format:
[<ElementName>/<SummaryName>]
Where <SummaryName> is the name of the Summary,
and <ElementName> is the name of the Element that the Summary is in.
Note that this is actually the general format of writing references to columns in other elements, but that referencing non-Summaries in this way requires a Lookup or Join to map values from one column to another.
One of the utilities of Summaries is that they are a single value so that no such mapping is needed!
Filtering and Summaries
Because Summaries are calculated based on the rows of data in the element that they are calculated in, filters that are applied to that element will also change the value of the Summary. This can be used to calculate a Summary based on a specific subset of data, like the data for Last Year.
Further, because Summaries can be referenced in other elements, this allows you to make compare values that have different sets of filters applied to them - like This Year vs Last Year.
Single Value Visualizations
Single Value Visualizations (aka KPIs) are a special type of Visualization meant to display a single value prominently (along with a comparison value, if needed.)
Calculations in the Values (and Comparison) fields of Single Value Visualizations are calculated in the exact same way as Summaries!*
*While Aggregate Values is checked. With it unchecked, there is a similar but different behavior beyond the scope of this post.
As a result, everything that we’ve learning so far applies to Single Value Visualizations as well!
Referencing Single Value Visualizations
Referencing the Values field of Single Value Visualizations works in the same way as referencing a Summary … however, you may find yourself asking “What is the name of my Single Value Visualization?”
Single Value Visualizations are actually named after the column placed into the Value field by default, such that referencing the Values field of a Single Value Viz uses the format:
[<Value>/<Value>]
where <Value> is the name of the column in the Value field.
However, note that you can change the name of your Single Value Viz under the Title tab of the Element format (paintbrush) tab on the left.
Hopefully this has been helpful to you!
If you have any thoughts or questions, please feel free to comment!
-Chandler Phelps, Sigma Support