Calculate with the Compare Table

Learning Objectives

After completing this unit, you'll be able to:

Use a compare table to perform calculations in CRM Analytics.
Add filters to dimensions.

The Exploration Goal

After discovering that your digital media sales may be headed for a fall, you’re going to start a sales contest focused on this product category. 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 digital media 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 CRM Analytics. The compare table is an effective tool for doing that with just a few clicks.

Calculate the Percentage of Total Sales that is Digital Media

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

(# of digital media 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.

Click .
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 ten 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 digital media opportunities. The filter is Product Family = Media Sales. Column C contains a calculated value with the percentage of total sales that is digital media sales. The calculation is (# of digital media 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.

In the Data tab, click Count of Rows.
Select the Sum aggregation function on the left.
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.

Next to Sum of #, click the menu control (v).
In the menu, select Add a Filter.
Select Close Date.
Select Custom.
Select the Absolute date range tab and the Between operator.
Set the From field to September 10, 2020, three months before today. (Remember, for our example, today is December 10, 2020.)
Set the To field to February 10, 2021—two months in the future.
Click Add.
To add the second filter, click the v next to Sum of # and select Add a Filter again.
Select Won, then select true, and finally, click Apply.

The number shows the volume of sales completed in the rolling year. The last thing you need to do is to rename your column.

Click the v next to Sum of #, and this time select Edit this Column.
In the Column Header field, enter All_Sales.
Click Apply, and then click Close to close the edit panel.

You have your first column! Time to add more.

Column B: Digital Media Won Opportunities

Add another column similar to the first one, but filtered on digital media sales.

Click the menu control (v) next to All_Sales (the new name of Column A) under Columns.
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 Family equals Digital Media.
Click the v for the second All Sales (Column B) and select Add a Filter.
In the search field, type product, then select Product Family.
Select Equals in the Operator pull-down menu, select Digital Media from the Values list, and click Apply.

Notice that you can scroll the Data panel. It should show that your second column now has three filters. Time to rename the column to Digital Media.

Under Columns, click the menu control (v) next to the second All_Sales (Column B).
Select Edit this Column.
In the Column Header field, enter Digital_Media.
Click Apply, and then click Close.

Now you’re ready to do the calculation!

Column C: % of Digital Media 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.

Click the menu control (v) next to # Digital_Media (Column B) under Columns, and select Clone column.
Click the menu control (v) of the new column, and select Edit this Column.
Change the column header to % Digital_Media.
In the formula field, put the formula defined above: B/A.
For the format, select Percentage.
Click Apply, and then click Close.

Notice that Column C under Columns 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!

In the Data tab, click the plus sign (+) under Group by.
Select Close Date.
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 digital media sales have been mediocre lately, even though they’re spiking this month (remember, it’s December 1!). And looks like they’re headed back down in January. c

We’re going to need that contest to get the digital media 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.

Clone the % Digital_Media column.
On the new column under Columns, click the menu control (v) and select Format Column.
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 digital media performance much more effectively.

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 digital media 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.

Click Save.
Enter D03 - Digital Media Sales % Evolution / Time as the title.
Select the My Exploration app.
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 CRM Analytics, you realized that your light digital media 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 digital media sales leaderboard that you can leverage to help your best sales rep share best practices.
A timeline that illustrates a slowdown in digital media sales.
A compare table that shows the percentage of digital media 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?

Resources

Explore and Visualize Your Data