# Calculate with the Compare Table

## Learning Objectives

After completing this unit, you'll be able to:
• Use a compare table to perform calculations in Analytics.
• Use the Contains filter on dimensions.

## The Exploration Goal

After discovering that your laptop sales may be headed for a fall, you’re going to start a sales contest focused on light laptops. To present your plan to executives, you need real numbers along with your charts to prove your point.

Here’s your goal for this unit: You want to show projections for the percentage of total sales versus light laptop sales for the next few months. Charts are great to see the big picture and trends, but there’s nothing like numbers to prove a point. Having both when you present your contest plan will put you ahead of the game.

This goal introduces a new concept that we haven’t covered yet: how to do calculations in Analytics. The compare table is an effective tool for doing that with just a few clicks.

## Calculate the Percentage of Total Sales that are Light Laptops

Before starting the exploration, you need to identify a formula to calculate the percentage of sales of all product families that are light laptop sales. It should look something like this:

(# of light laptop sales / # of all sales) x 100

Let’s get those numbers and perform the calculation.

## The Compare Table

You still want to do analysis on opportunities and product data, so open the DTC Opportunity dataset to start a new exploration. First things first: Change the visualization to a compare table.
1. Click .
2. Choose Compare Table.

As shown when you hover over its icon, the compare table is a column-based visualization. It’s different from the visualizations you’ve seen before. Each column has its own filters and measure, and some of the columns can be used to do calculations. You can also add up to four groupings that are applied to every column, as you’ll see later.

Let’s create a compare table with the following columns.
Column A
Contains all won opportunities.
Column B
Contains all won light laptop opportunities. The filter is Product Family = Light Laptop.
Column C
Contains a calculated value with the percentage of total sales that are light laptop sales. The calculation is (# of light laptop sales / # of all sales) x 100

## Column A: All Won Opportunities

In the initial state, the compare table has only one column showing a number corresponding to the Count of Rows. You want the number of sold products, so you’ll change the measure.
1. Click Count of Rows.
2. Select the Sum aggregation function on the left.
3. Select the # (quantity, not amount) measure on the right.
Notice that the name of your measure, Sum of #, is also the column name. The Compare Table lets you apply filters per column. Now you’re ready to apply two filters to your first column.
• One filter is the past few months and next few months.
• Another filter is won opportunities.
1. Next to Sum of #, click the menu control (v).
3. Select Close Date.
4. Select the Absolute date range tab and select Between.
5. Set the From field to January 1, 2016, three months before today. (Remember, for our example, today is April 1, 2016.)
6. Set the To field to June 30, 2016—several months in the future.
8. To add the second filter, click the v for Sum of # again.
9. Select Won, then select true, and finally, click Add.
The number shows the volume of sales completed in the rolling year. The last thing you need to do is to rename your column.
1. Click the v next to Sum of #, and this time select Edit this Column.
2. In the Column Header field, enter # All Sales.
3. Click Apply, and then click Close to close the edit panel.

## Column B: Light Laptop Won Opportunities

Add another column similar to the first one, but filtered on light laptop sales.
1. Click the menu control (v) next to # All Sales (the new name of Column A) under Measures.
2. Select Clone Column.

When a cloned column is created, it copies all the filters and measures from the column that it’s created from. Easy, right? Now we’ll add a filter to Column B where Product Name contains Light laptop.

3. Click the v for the second # All Sales (Column B) and select Add a Filter.
4. In the search field, type product, then select Product Name.
5. Select Contains in the pulldown menu, type light laptop, and click Add.

Notice that you can scroll the Measures panel. It should show that your second column now has three filters. Time to rename the column to # Light Laptop Sales.

6. Under Measures, click the menu control (v) next to the second # All Sales (Column B).
7. Select Edit this Column.
8. In the Column Header field, enter # Light Laptop Sales.
9. Click Apply, and then click Close.

Now you’re ready to do the calculation!

## Column C: % of Light Laptop Won Opportunities

Up next, you’ll add one more column and give it a formula. But first, let’s cover a bit of the formula syntax. With a formula, you can perform a calculation using data in other columns, referencing each column by letter. Every column has a letter, starting with A and progressing alphabetically. The following graphic shows how the formula syntax maps onto the table:

Now let’s add a column with a formula.
1. Click the menu control (v) next to # Light Laptop Sales (Column B) under Measures, and select Clone column.
2. Click the menu control (v) of the new column, and select Edit this Column.
3. Change the column header to % Light Laptop Sales.
4. In the formula field, put the formula defined above: B/A.
5. For the format, select Percentage.

6. Click Apply, and then click Close.

#### Note

Notice that Column C under Measures is not clickable. That’s because it’s a formula column and you can’t change its measure. Similarly, if you open the menu for Column C, the filter option isn’t available because you can’t add filters on a formula column.

## Group in a Compare Table

You’ve just seen how to do a percentage calculation using a compare table. In your original goal, you wanted to calculate this percentage for each month. Do you remember the three basic actions of a query? Aggregation, grouping, and filtering. Did you already group in your compare table? Nope, so let’s do it now!
1. Click the plus sign (+) under Group by.
2. Select Close Date.
3. Select Year-Month.

Voilà! The query is grouping by the closed month and getting the results of each column for all groups. The numbers show us that light laptop sales have been mediocre lately, even though they’re spiking this month (remember, it’s April 1!). And looks like they’re headed back down in May and June, as we feared.

We’re going to need that contest to get the light laptops results DTC’s looking for, to boost the product in the home and education markets. To help convince the rest of the team, let’s turn the numbers into a picture.

## Show Results as Bars

Let’s take this exploration a little further before we wrap it up. You can show the results of a formula as bars, which makes it easier to grasp.
1. Clone the % Light Laptop Sales column.
2. On the new column under Measures, click the menu control (v) and select Format Column.
3. In Column Properties, select Show as Bars.

With this visualization as part of your table, you’ll be able to make your point about laptop performance much more dramatically.

The next step: to find out how to get more deals closed this month. But you’re running out of time. You need to jump in a taxi and head to the airport, so you can’t continue your exploration on desktop. In the next module, you’ll see how to keep going with your research, but this time on your phone.

## Save Your Exploration in a Lens

You’ve reached your exploration goal: You now have numbers and pictures that show light laptop sales need help. You’re confident that your sales contest plan will be widely approved within DTC Electronics. So before getting in that taxi, save your exploration in a lens.
1. Click .
2. Enter D03 - Light Laptop Sales % Evolution / Time as the title.
3. Select the My Exploration app.
4. Click Save.

## Get Ready to Go Mobile

In this unit, you’ve learned about the compare table. The compare table is a column-based visualization. Some of the columns can use a formula to calculate values from other columns. The main concept we want to drive home is that you can use the compare table to do calculations.
This concludes the desktop exploration module. By diving into your data like you can do only in Analytics, you realized that your light laptop sales are heading for a fall. You’ll start a sales contest to keep sales on track, and you can use all your lenses to back up your plan. So far, you have: