trailhead

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 Table selection icon.
  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).
  2. In the menu, select Add a Filter.
  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.
  7. Click Add.
  8. To add the second filter, click the v for Sum of # again.
  9. Select Won, then select true, and finally, click Add.
Add the filters Opp Close Date and Opp Is Won
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.
    Compare Table menu

You have your first column! Time to add more.

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.
    Creating a filter for Column B of the Compare Table

    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:

Column C of the Compare table is calculated by dividing value in column B by value in column A.

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.

    Editing the column name, formula, and data format to show the percentage of sales

  6. Click Apply, and then click Close.
Note

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.
The compare table groups the results by the Year-Month of the close date

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.
TheColumn Properties lets you show data 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 compare table shows % Light Laptop Sales values as bars

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 Save button.
  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:
  • A laptop sales leaderboard that you can leverage to help your best sales rep share best practices.
  • A timeline that illustrates a slowdown in laptop sales.
  • A compare table that shows the percentage of laptop sales through time.

Be proud of yourself—you deserve your badge! You might be standing in the taxi line, but your work’s not finished yet. And you don’t have time to wait until you’re back in the office. That’s why, in the next module, we’ll show you how to explore data on your mobile device, so you can take action on the go. A new world of explorations will open up to you. Ready?

retargeting