# How to set up a pivot table

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
1. Drag the `Region` column into the rows field of your pivot
2. 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.)
1. Drag `Product Type` into ‘Pivot Rows’ below `Store Region`

### 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`
1. 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.