How to set up a pivot table

[benoneil]

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.

1. Create a new ‘Pivot’ child element
2. Drag Order Date into the Pivot Columns field

• Go ahead and ‘truncate’ the Order Date ‘graining’ to the year level

3. Drag the Region column into the rows field of your pivot
4. Drag the Revenue and COGS 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.)
  
  

5. Drag Product Type into ‘Pivot Rows’ below Store 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.

1. Right click on any of the Sum of COGS columns and click Add New Columns.

• Go ahead and rename the columns % Margin

2. In our formula bar at the top lets reference our Sum of Revenue and Sum 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

1. Right click on the % Margin column and click Conditional Formatting
2. 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.

1. Let’s start by creating a new column called % of Region Revenue
2. Click on the columns you just created and in the formula bar define the numerator Sum of Revenue
3. 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.