Add table calculations to a data table

📘
This article applies to the following Customer Insights roles: Developer

Customer Insights' default Explores include the Count measure and (in two cases, anyway) the Count Distinct measure. Both of these measures are useful in analyzing data and in constructing visualizations. However, there will likely be times when you want to do something besides simply counting the number of Xs in a dataset. That’s where table calculations come in.

Table calculations (created using a formula syntax similar to that used in Microsoft Excel) provide a way for you to add custom measures to a data table (and, by extension, to a visualization). For example, suppose you have a table similar to this one, a table that shows legacy sign-ins on a month-by-month basis:

Let’s further suppose that you've set a goal of 35,000 sign-ins per month (420,000 sign-ins for the year). From the table, it’s easy to see that you met the overall goal: you had 459,070 sign-ins during the year. But how often did you meet the monthly goal, and by how much did you meet (or fail to meet) that goal? That’s not quite as easy as to say, at least not without doing a little math.

Doing a little math is good; adding a table calculation is better. When you add a table calculation, you add a new column to the table; that column contains whatever data is derived from the table calculation. To see what that means in real life, complete the following procedure:

In the Data section of your Look, click Calculations:
In the Table Calculations dialog box, replace the title Calculation 1 with the title Percent Difference from Goal:
Click Default Formatting and then click Percent (0) 123,457%:

In case you’re wondering, we want to show the percent difference between the target value 30,000 and the actual number of sign-ins. We want the value specific as a percentage (and without decimal points), which is why we selected Percent (0) 123,457%.

In the formula section (directly below the title) type $. You should see something similar to this:

This is the list of dimensions and measures that are used in your query. To determine the percent difference, we need to take the value of the Event Fact Count field and divide it by 35,000. In the dialog box, select Count ${event_fact.count}. The formula field will look like this:
Now complete the formula by adding /35000 and then click Save Table Calculations:
Your data table immediately redraws itself to look like this:

Now it’s much easier to see whether we succeeded or failed to reach our goal, and by how much (good or bad) we deviated from that goal.

But what if you wanted to know more about sign-in trends, what if you wanted to know how signups for a given month compared with signups for the previous month? That's an interesting question, and Customer Insights includes a nifty feature - offset - that helps you answer that question.

Using table calculation offsets

By default, table calculations compare values in the same row. In this case, however, we want to compare values in different rows; for example, we want to compare the total sign-ins in the June row with the total sign-ins in the May row. With an offset, we can compare a row with the previous row (by setting the offset to 1) or with the next row (by setting the offset to -1). Here’s how we do that:

In the Data section of your Look, click Table Calculations.
In the Title field, type Month-to-Month Trends.
Click the formatting list and then click Decimals (0) - 1,235.
In the Table Calculations dialog box, type $.
Select Count ${event_fact.count}.
Type - o. and then click offset(Any,Number). Your formula will Look like this:
With the cursor inside the parentheses, type $ and then click Count ${event_fact.count}:
Type ,1 and then click Save Table Calculations:

Here’s your new visualization:

📘
If you click Calculations, you’ll bring up the Table Calculations dialog box. From there, you can apply formatting, change the column title, etc.