Sigma allows you to easily build pivots table with an Excel like look and feel. Moreover, these pivots have the added benefit of being live against your warehouse and being able to scale to near limitless amounts of data. Here we will show you the basics of pivot creation.
Feel free to you use your own data but if you would like to follow this post you can find the data we are using by navigating to the Sigma Sample Database and then navigating to EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA. Once you have created a new workbook, go ahead and create columns for Revenue
([Quantity] * [Price]) and COGS
([Quantity] * [Cost]).
1. Basic Pivot Set Up
Let’s start by building a pivot with Year
as our columns and Region
as our rows. We will use COGS
and Revenue
as our values.
 Create a new ‘Pivot’ child element
 Drag
Order Date
into the Pivot Columns field
 Go ahead and ‘truncate’ the
Order Date
‘graining’ to the year level
 Drag the
Region
column into the rows field of your pivot  Drag the
Revenue
andCOGS
columns into the ‘Values’ field
 The default aggregation is sum but if you click on the drop down and click ‘Set Aggregate’ you can access other prebuilt aggregations (median, average, distinct etc.)
 Drag
Product Type
into ‘Pivot Rows’ belowStore Region
 Note that the pivot automatically updates with the additional field.
2. Arbitrary Calculations
In addition to the the prebuilt aggregations we can also create arbitrary calculations using the formula bar and Sigma’s formula library. Let’s calculate % Margin
column using our Sum of Revenue
and Sum of COGS
columns.
 Right click on any of the
Sum of COGS
columns and click Add New Columns.
 Go ahead and rename the columns
% Margin
 In our formula bar at the top lets reference our
Sum of Revenue
andSum of COGS
columns to calculate% Margin
 Formula:
([Sum of Revenue]  [Sum of COGS]) / [Sum of Revenue]
 Go ahead and format the column as a percent
3. Conditional Formatting
Now that we have a % Margin
column lets add some conditional formatting
 Right click on the
% Margin
column and click Conditional Formatting  Default calculation is Not Null but let’s change it to reflect the value of
% Margin
 Click on Color Scale tab on the Conditional Formatting Bar
 Change the Color Range as desired
4. Subtotals
Subtotals will automatically calculate in our pivot, notice that we have a region level total in addition to a grand total. Here we will show you how you can reference those totals to calculate the % of revenue coming from each product line in each region.
 Let’s start by creating a new column called
% of Region Revenue
 Click on the columns you just created and in the formula bar define the numerator
Sum of Revenue
 Now lets fill out the denominator

Type
[Sum of Revenue] / [Sum of Revenue
into the formula bar. When you do you should see a number of aggregate options from the pivot you can reference including the grand total, parent row totals and row totals 
You can read more about the definitions for these different aggregates here but below if a quick reference

Let’s choose
[Sum of Revenue (Parent Column Total)]
as our denominator so we can calculate % of revenue from each product line 
Alternatively if you choose
[Sum of Revenue (Row Total)]
we would calculate total revenue for a year as a % of revenue across all years 
Again, go and format that new column as a %
That’s it for our quick guide, if you’d like to learn more about pivots take a look at our documentation.