Why table calculations compute across the view in Tableau - Why It Works This Way

Overview - Why table calculations compute across the view

What is it?

Table calculations in Tableau are special formulas that work on the data already shown in your chart or table. They calculate results based on the arrangement of data visible in the view, not the original raw data. This means they depend on how you organize rows, columns, and filters in your visualization.

Why it matters

Without understanding that table calculations compute across the view, you might get confusing or wrong results when analyzing data. Knowing this helps you control how calculations flow and appear, making your dashboards accurate and insightful. Without this concept, you could misinterpret data trends or totals, leading to poor decisions.

Where it fits

Before learning this, you should know basic Tableau concepts like dimensions, measures, and how to build simple views. After this, you can explore advanced calculations, level of detail expressions, and dashboard interactivity to create powerful insights.

Mental Model

Core Idea

Table calculations work by looking at the data as it is arranged in your view, computing results across the visible rows and columns.

Think of it like...

Imagine you have a spreadsheet printed on paper. Table calculations are like doing math only on the numbers you see on that paper, moving left to right or top to bottom, rather than going back to the original data stored on your computer.

┌─────────────┐
│   Tableau   │
│   View      │
│ ┌─────────┐ │
│ │ Data    │ │
│ │ arranged│ │
│ │ in rows │ │
│ │ & cols  │ │
│ └─────────┘ │
│             │
│ Table Calc  │
│ computes →  │
│ across view │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Table Calculation

Concept: Introduce the idea of calculations that happen after data is placed in the view.

Table calculations are formulas you add in Tableau that work on the data shown in your chart or table. Unlike regular calculations that use raw data, table calculations use the data as it appears after filtering and arranging.

Result

You get new numbers based on visible data, like running totals or percent of total, that update as you change the view.

Understanding that table calculations work on displayed data helps you see why changing the view changes the calculation results.

2

Foundation

How Data is Arranged in the View

Concept: Explain how Tableau organizes data into rows and columns before calculations.

When you build a view, Tableau places data into a grid of rows and columns based on your chosen dimensions and measures. This arrangement defines the order and grouping of data points.

Result

The view becomes a structured table where each cell represents a data point or aggregate.

Knowing the data layout is key because table calculations use this layout to decide how to compute results.

3

Intermediate

Direction of Table Calculations

🤔Before reading on: do you think table calculations always compute left to right, or can they compute in other directions? Commit to your answer.

Concept: Table calculations can compute across rows, down columns, or in custom directions depending on settings.

Tableau lets you choose how calculations move through the data: across the table (left to right), down the table (top to bottom), or using specific partitioning and addressing fields. This controls which data points are included in each calculation step.

Result

You can create running totals horizontally, percent of total vertically, or more complex patterns by adjusting direction.

Understanding direction lets you tailor calculations to your analysis needs and avoid unexpected results.

4

Intermediate

Partitioning and Addressing Explained

🤔Before reading on: do you think partitioning splits data into groups or combines all data together? Commit to your answer.

Concept: Partitioning divides data into groups for separate calculations; addressing defines the order within those groups.

Table calculations use two concepts: partitioning (which breaks data into chunks) and addressing (which defines the order inside each chunk). For example, you might partition by region and address by month to calculate running totals per region.

Result

Calculations happen independently within each partition, respecting the order set by addressing fields.

Knowing partitioning and addressing helps you control calculation scope and sequence precisely.

5

Intermediate

Impact of Filters and Sorting on Calculations

Concept: Filters and sorting change the data visible in the view, affecting table calculations.

Filters remove data before the view is built, so table calculations only see filtered data. Sorting changes the order of data points, which can change calculation results like running totals or rankings.

Result

Changing filters or sort order can dramatically change table calculation outputs.

Recognizing how filters and sorting affect calculations prevents confusion when results change unexpectedly.

6

Advanced

Why Table Calculations Depend on the View

🤔Before reading on: do you think table calculations use raw data or the data after view arrangement? Commit to your answer.

Concept: Table calculations compute on the data after Tableau builds the view, not on the original data source.

Tableau first queries and aggregates data based on your dimensions and filters, then places it in the view. Table calculations then run on this arranged data. This means they cannot access data outside the view or before filtering.

Result

Calculations reflect the current view state, making them dynamic but also limited to visible data.

Understanding this order clarifies why some calculations need different approaches, like level of detail expressions, when raw data access is required.

7

Expert

Advanced Control with Compute Using and Custom Directions

🤔Before reading on: do you think you can customize exactly which fields table calculations compute over? Commit to your answer.

Concept: Tableau allows fine control over table calculations by specifying exactly which dimensions to compute over using 'Compute Using' and custom directions.

You can tell Tableau to compute a calculation over specific dimensions, ignoring others, by setting 'Compute Using' options or writing custom table calculation formulas. This lets you create complex calculations like moving averages across selected fields or nested partitions.

Result

You gain precise control over calculation behavior, enabling sophisticated analysis beyond default settings.

Mastering compute directions unlocks powerful, flexible calculations that adapt to complex data layouts.

Under the Hood

Tableau first queries the data source applying filters and aggregations to build the view's data grid. Table calculations then run on this grid, moving through cells in the order defined by addressing and partitioning. They do not re-query the data source but operate on the in-memory arranged data, which makes them fast but dependent on the view layout.

Why designed this way?

This design separates data retrieval from calculation, improving performance and interactivity. It allows quick recalculations as users change the view without hitting the database again. Alternatives like recalculating on raw data would be slower and less responsive.

┌───────────────┐
│ Data Source   │
│ (raw data)    │
└──────┬────────┘
       │ Query with filters
       ▼
┌───────────────┐
│ View Data     │
│ (aggregated,  │
│ filtered)     │
└──────┬────────┘
       │ Table Calculation runs here
       ▼
┌───────────────┐
│ Final Results │
│ (computed on  │
│ view data)    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do table calculations always use the original raw data? Commit yes or no.

Common Belief: Table calculations use the original raw data directly for their computations.

Reality: Table calculations work only on the data as it appears in the view after filtering and aggregation, not on raw data.

Why it matters: Believing they use raw data can cause confusion when calculations change unexpectedly after filtering or rearranging the view.

Quick: Do table calculations compute results independently of the view layout? Commit yes or no.

Common Belief: Table calculations produce the same results regardless of how data is arranged in rows or columns.

Reality: The layout of data in the view directly affects how table calculations compute results, including their direction and partitioning.

Why it matters: Ignoring layout effects can lead to wrong assumptions about calculation outputs and misinterpretation of data.

Quick: Can table calculations access data outside the current view? Commit yes or no.

Common Belief: Table calculations can access and compute using data points not shown in the current view.

Reality: Table calculations are limited to the data visible in the view and cannot reference hidden or filtered-out data.

Why it matters: Expecting access to all data can cause errors or incomplete calculations, requiring other techniques like level of detail calculations.

Quick: Do filters always affect table calculations the same way? Commit yes or no.

Common Belief: All filters affect table calculations equally by removing data before calculation.

Reality: Filters that remove data before the view (context filters) affect table calculations, but some filters applied after the view (like dimension filters) may not affect them the same way.

Why it matters: Misunderstanding filter impact can cause unexpected calculation results and confusion during analysis.

Expert Zone

1

Table calculations can be nested, allowing one calculation to use the result of another, enabling complex multi-step analyses.

2

The order of dimensions in the view affects partitioning and addressing, so rearranging fields can change calculation results subtly.

3

Using 'Restarting Every' options in table calculations lets you reset calculations at specific dimension boundaries, a powerful but often overlooked feature.

When NOT to use

Table calculations are not suitable when you need calculations based on raw data regardless of view filters or layout. In such cases, use Level of Detail (LOD) expressions or data source calculations instead.

Production Patterns

In real dashboards, table calculations are often used for running totals, percent of total, rankings, and moving averages. Experts combine them with parameters and dynamic sorting to create interactive, user-driven reports.

Connections

Level of Detail (LOD) Expressions

Complementary concept that computes aggregations independent of the view layout.

Understanding table calculations clarifies why LOD expressions are needed when calculations must ignore the view's arrangement or filters.

Spreadsheet Formulas

Similar pattern of computing results based on visible cell arrangement.

Knowing how spreadsheet formulas recalculate based on cell position helps grasp how table calculations depend on data layout.

Data Pipeline Processing

Table calculations represent a post-processing step after data retrieval and aggregation.

Recognizing table calculations as a final step in the data pipeline helps understand performance and design tradeoffs in BI tools.

Common Pitfalls

#1 Assuming table calculations use raw data ignoring filters.

Wrong approach:

WINDOW_SUM(SUM([Sales])) without considering filters or view layout.

Correct approach:

Use context filters or LOD expressions if calculation must ignore certain filters.

Root cause:

Misunderstanding that table calculations operate only on filtered, aggregated data in the view.

#2 Not setting correct compute direction causing wrong totals.

Wrong approach:

Using default table calculation direction without adjusting 'Compute Using' for running total across columns.

Correct approach:

Set 'Compute Using' to the correct dimension (e.g., across table) to get accurate running totals.

Root cause:

Ignoring how calculation direction affects the order of computation.

#3 Expecting table calculations to include hidden or filtered-out data.

Wrong approach:

Creating percent of total calculation assuming all data is included despite filters.

Correct approach:

Use LOD expressions or adjust filters to include necessary data before table calculation.

Root cause:

Not realizing table calculations only see data present in the current view.

Key Takeaways

Table calculations compute results based on the data as arranged and visible in the Tableau view, not on raw data.

The direction and partitioning of table calculations control how they move through and group data in the view.

Filters and sorting affect the data in the view and therefore impact table calculation results.

Understanding the difference between table calculations and other calculation types like LOD expressions is key to accurate analysis.

Mastering compute directions and partitioning unlocks powerful, flexible data insights in Tableau dashboards.