What Are Calculated Values and Table Calculations in Tableau?

Calculated Values and Table Calculations

You can modify and compare table calculations. Calculated Values and Table Calculations allow you to add new data to your Tableau workbook, but the way you add the data, and where the calculations occur, is different for each method. Use table calculation functions to customize table calculations. They are computations that are applied to the values in the entire table and are often dependent on the table structure itself.

Calculated Values are defined by entering a formula into Tableau’s formula editing dialog box. For example, if you have gross margin dollars and sales dollars in your source data, you may want to add a new field called Gross Margin Percent by creating a calculated value. The formula to create the gross margin percent is: sum ([ gross margin dollars]) / sum ([ sales dollars]).

The Sum aggregation function in front of each field name tells the source database what to return to Tableau. The calculated values are normally processed at the data source. What this means is that the power of your database server is used to do the heavy number crunching, with the database returning only what is needed for Tableau to build the visualization.

Table calculations are created in a different way by using your data visualization as the source for the formula.

Pre-defined Quick Table Calculations remove the need for you to create the formula manually, but these are always processed locally because they rely on the data presented in your view to derive the formula.

Calculated values can also include table calculation functions. These are the functions you use in calculated values that are processed locally just like Quick Table calculations.

Calculated Values Work:

Calculated Values can be used to generate numbers, dates, date-times, or strings. All calculated values require the following elements:

• Functions— including aggregate, number, string, date, type conversion, logical, user and table calculation types
• Fields— selected from the data source
• Operators— for math and comparison of values, dates, and text
• Optional elements can be added within the formula dialog box including:
• Parameters— for creating formula variables that are accessible to information consumers
• Comments— for documenting formula syntax and notes within the formula dialog box

Start the formula dialog box via the main menu using the Analysis/ Created Calculated Field option or by right-clicking on a field . The formula dialog is where you enter the functions, operators, and parameters to create the logic for your formula. Alternatively, right-clicking a field in the dimensions or measures shelves opens the formula dialog box as well, but also includes that field is already entered in the formula editing area.

People experienced at writing SQL script or creating spreadsheet formulas normally have very little difficulty in learning how to write formulas in Tableau. Those with very little experience in writing formulas may need more help. Tableau provides assistance via a real-time formula editor and a help window in the formula editing window, as well as an online manual that is accessible from the editing window.

Related Page: How To Improve Appearance To Convey Meaning More Precisely In Tableau?

Table Calculations Work:

Table calculations are the computational workhorse of Tableau. Table calculations are derived from the structure of the data included in your visualization, so table calculations are dependent on the source worksheet view contained in your workbook. That means these calculations are always derived locally by using your personal computer’s processor to return the result.

Understanding exactly how Table Calculations work takes a little time because Table Calculations can change as your visualization is altered. As with any new concept, after you create some Table Calculations you’ll get comfortable with how they behave in different situations. Tableau’s online manual has a large number of examples where you can view and het some good basic introduction.

Creating a Table Calculation requires that you have a worksheet with a visualization. A good way to create them is to right-click on a measure pill used in the view to expose the Quick Table Calculation menu. Quick Table Calculations are provided for:

• Running total
• Difference
• Percent difference
• Percent of total
• Moving average
• YTD total
• Compound growth rate
• Year over year growth
• YTD growth

Depending on the view of the data included in your worksheet some of these may be unavailable because your worksheet view doesn’t support the calculation. Unavailable calculations will be visible in the menu, but will appear grayed-out.

Related Page: What Are The Advanced Chart Types In Tableau?

A Word on Calculations and Cubes:

Tableau connects to relational databases, spreadsheets, columnar-analytic databases, data services, and data cubes (multi-dimensional data sources). Data cubes are different from regular database files because they pre-aggregate data and define hierarchies of dimensions in a specific way.

If you need to access pre-aggregated data that is stored in a multi-dimensional data source, you can still perform calculations using Tableau formulas or create formulas using the standard query language of multi-dimensional databases, Multidimensional Expressions (MDX). The syntax is a bit more complex but MDX also provides the ability to create more complex formulas. If you desire to learn more about options for creating calculations when accessing Data Cubes, refer to Tableau Software’s quick start guide for Creating Calculated Fields-Cubes. Tableau’s behavior when you connect it to a data cube is different because the cube controls aggregation. For example, date fields behave differently because the cube controls the date aggregation in specific ways.

Related Pages:

Tableau Server Tutorial

Tableau Interview Questions

Tableau Sample Resumes

List Of Tableau Courses:

Tableau Advanced

Tableau Server

Data Visualization and Dashboarding Fundamentals