Sigma combines with the unlimited power of the cloud data warehouse and the familiar feel of a spreadsheet; no limit on the amount of data you wish to analyze. Sigma is awesome for users of Excel and even better for customers who have millions of rows of data.
Typical audience for this QuickStart are users of Excel, common Business Intelligence or Reporting tools and semi-technical users who want to try out or learn Sigma. Everything is done in a browser so you already know how to use that. No SQL or technical skills are needed to do this QuickStart.
Through this QuickStart we will walk through why use a Pivot Table, how to use Sigma to create one adding conditional formatting and drilling down on table data.
We will be working with some common sales data from our fictitious company ‘Plugs Electronics'. This data is provided to you automatically.
We will build a Workbook that looks like this:
It is important to understand what a Pivot Table (Pivot) is and how it is different from a typical table that might use grouping to provide the desired result set.
A Pivot Table is an interactive way to quickly summarize large amounts of data. You can use a Pivot Table to analyze numerical data in detail, and answer unanticipated questions about your data. A Pivot Table is especially designed for:
Tables tend to provide a flat organization of the data, although grouping and other features can make less obvious to users who are unfamiliar with the differences.
For example, let's assume we want to look at Profit and Profit Margin by Regions and Product Type over time. We have the required columns in our source data as shown below and could possibly satisfy the requirement by grouping the data but the end result will not be easy for the viewer to interpret and they may have to make multiple clicks to orient the table to suit their interests.
The grouped output of this may look something like this and you can easily see how the consumer may be frustrated:
Let's create a Pivot instead.
In Sigma, open the Workbook
Plugs Sales and place it in edit mode. We should still have the Page tab called
Data that has the "Plugs Sales" Table on it.
new Page and name it
+ icon to open the Editor Panel. This panel gives you access to all the objects possible to add to a Page. Select
Pivot Table and for the source of data; we will use the Table on the Data page
Notice that there are a few options for the source data. Reusing data that has already been queried from the Connection can help improve performance by limiting the number of queries sent to the Cloud Data Warehouse (CDW).
We now have our Pivot Table and can begin working just like a normal spreadsheet. Get ready, you may be pleasantly surprised by how easy this is in Sigma.
First we will want to drag
Store Region and
Product Type to our
Pivot row. Notice that the Pivot table appears as we build? Already the resultant table is making more sense than our grouping example earlier:
Next we will want to drag
Date to the
Pivot column. We also want to
truncate the date to
Depending on if you sorted your source data the most current year may not be shown first. Lets change to sort order to have our most recent year at the far left. You can either do this from the Pivot Column panel using the
dropdown next to the
Year of Date field, or we can do it directly in the Pivot Table itself by selecting the dropdown next to ‘Year of Date' column header and clicking sort ‘Year of Date':
Lastly, we will want to add our values which we want to look at. Drag the
ProfitMargin fields into the
Profit Margin is a little off, so let's change it to be an Average instead of a Sum by clicking the dropdown menu next to
Sum of Profit Margin and selecting an
aggregation of AVG:
You also may want to change the Pivot Row and Column names to suit your needs.
Let's say we only want to see the current and last year's data along with Totals. Just
enable the filter the Pivot as shown below:
Set the desired Date range:
Apply Conditional Formatting to make the potential problems stand stand out.
Enable Conditional Formatting:
Se the Conditional Format rule as shown to high-light the Margin less than or equal to 25 percent in red:
That's it; we now have a beautiful Pivot Table we can explore:
The presentation of the Pivot is just the starting point for the user who most likely cares about spotting problems or trends and taking action. From the last exercise we can see that the East Region / Entertainment category is selling below margin guidelines. We want more details!
Right click on the
Entertainment product type in the Pivot and select
Keep only Entertainment:
right click on the
Entertainment product type in the Pivot and select
Show underlying data. You could also just click the
expand icon as shown below with the red arrow:
We are shown only the subset of data specified and can easily work with all the row level detail to see which transactions are being sold at lower than desired margins; in this case, televisions.
You can get back to the original Pivot by either clicking the
Back icon or deleting any active filters you set (either click the x or use the 3-vertical-dot menu) as shown below:
In this QuickStart we covered why you might use a Pivot Table, how to use Sigma to create one adding conditional formatting and drilling down on table data.
Additional Resource Links
Help Center Home