Author avatar

Deepika Singh

Create Type-in/Ad-hoc Calculations in Tableau

Deepika Singh

  • May 8, 2020
  • 5 Min read
  • 102 Views
  • May 8, 2020
  • 5 Min read
  • 102 Views
Data
Data Analytics
Business Intelligence
Tableau

Introduction

In business intelligence visualization, often the reports are presented in a manner that requires type-in or ad-hoc calculations to be performed. These are calculations that make a quick summary of the expression or formula you may want to implement. They allow you to create a calculation in the shelf, and are applicable only in the view where they are created. In this guide, you will learn how to create ad-hoc calculations.

Data

The data to construct an ad-hoc calculation can be of any type, such as a measure, dimension, date etc. This guide will use the sample superstore data source available in the Tableau repository. There are three sheets: People, Orders, and Return, that have been joined to fetch the data. In the subsequent sections, you’ll work with different ad-hoc calculations.

Ad-hoc Calculations: Rows

The ad-hoc calculations can be constructed in Rows and Columns shelves, as well as in the Marks and Measures. To start with, double click on the Rows shelf to either create a temporary calculation or to call a measure from the Measures shelf.

Output:

Imgur

Typing the first few letters will display the names as shown below.

Output:

Imgur

The first calculation you’ll do is to sum the variable Profit which will result in the image below.

Output:

Imgur

Ad-hoc Calculations: Columns

You can also create an ad-hoc calculation on the Columns shelf and the Marks card. Drag the variable Sub-Category into the Rows shelf and the ad-hoc calculation can be created on the Columns shelf.

Output:

Imgur

In this example, you are calculating the profit margin by dividing the variable, Profit, with Sales. The column shelf is formatted to percentage, and the variable, Category, is placed in the Color option of the Marks card. This will generate the output below.

Output:

Imgur

Ad-hoc Calculations: Measures

To begin, drag Segment into the Rows shelf and the measure, Sales, into the Text label in the Marks card.

Output:

Imgur

The next step is to double click on the measure and edit the field with calculation. In this case, you’ll divide Sales to get the value in millions, as shown below.

Output:

Imgur

The above step will create the following output.

Output:

Imgur

Converting Ad-hoc to an Actual Calculation

Ad-hoc calculations are temporary calculations that don’t get saved in the workbook unless used in the view. To convert them into actual calculations, you simply need to drag the ad-hoc calculation into the Measures or Dimensions shelf. Doing this will prompt you to rename the calculation. This is shown below.

Output:

Imgur

In this example, you’ll name it Profit Ratio.

Output:

Imgur

Sorting Ad-hoc Calculations

You’ll begin by dragging the Sales variable into the Columns shelf, and Region into the Rows shelf.

Output:

Imgur

The next step is to double click on the blank space in the Rows shelf and create an ad-hoc calculation consisting of the RANK() function to sort the values.

Output:

Imgur

Next, right click on the ad-hoc calculation and make it discrete.

Output:

Imgur

Drag it before the variable, Region, which is now sorted in the descending order of Sales value.

Output:

Imgur

Conclusion

In this guide, you learned how to create ad-hoc calculations in Tableau. You also learned the different options for dealing with these ad-hoc calculations. This will help in strengthening your descriptive analytics capabilities.

To learn more about visualization and data analysis using Tableau, please refer to the following guides:

1