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]).
Let’s start by building a pivot with
Year as our columns and
Region as our rows. We will use
Revenue as our values.
- Create a new ‘Pivot’ child element
Order Dateinto the Pivot Columns field
- Go ahead and ‘truncate’ the
Order Date‘graining’ to the year level
- Drag the
Regioncolumn into the rows field of your pivot
- Drag the
COGScolumns 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.)
Product Typeinto ‘Pivot Rows’ below
- Note that the pivot automatically updates with the additional field.
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 COGScolumns and click Add New Columns.
- Go ahead and rename the columns
- In our formula bar at the top lets reference our
Sum of Revenueand
Sum of COGScolumns to calculate
([Sum of Revenue] - [Sum of COGS]) / [Sum of Revenue]
- Go ahead and format the column as a percent
Now that we have a
% Margin column lets add some conditional formatting
- Right click on the
% Margincolumn and click Conditional Formatting
- Default calculation is Not Null but let’s change it to reflect the value of
- Click on Color Scale tab on the Conditional Formatting Bar
- Change the Color Range as desired
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
[Sum of Revenue] / [Sum of Revenueinto 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
[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.