Tableau Session 02: Intermediate Visual Analytics

DATA MODELING
ADVANCED CHARTS
CALCULATIONS
PARAMETERS
FILTERING

Learning Goals

By the end of this session, students will be able to:

  • Connect Tableau to relational databases such as PostgreSQL and Microsoft SQL Server
  • Understand the difference between live and extract connections
  • Combine data using relationships, joins, unions, and blending
  • Understand Tableau’s logical and physical data model
  • Create calculated fields at different levels of evaluation
  • Use basic Tableau functions in analytical calculations
  • Apply Level of Detail (LOD) expressions correctly
  • Use parameters for interactive analysis
  • Understand Tableau’s filter order of operations
  • Build advanced analytical charts such as area charts, bullet charts, and box plots
  • Design interactive dashboards with actions, filters, and tooltips

Overview

This session moves from basic chart building to analytical modeling and interactive dashboard design.

You will learn how Tableau:

  • Connects to relational databases
  • Combines data from multiple tables and sources
  • Evaluates calculations at different levels of detail
  • Uses filters and parameters to control analytical logic
  • Supports advanced chart types for deeper insight
  • Enables interactive dashboards for exploratory analysis
Note

By the end of this class, you should be able to model multi-table data correctly, create advanced analytical views, and build interactive dashboards that respond to user input in a controlled and predictable way.

Introduction: From Flat Tables to Analytical Models

In real-world analytics, data rarely exists in a single flat file.

Business questions usually span multiple entities such as:

  • Customers
  • Orders
  • Products
  • Locations
  • Time

Poor data modeling can lead to:

  • Incorrect aggregations
  • Duplicated values
  • Slow dashboards
  • Misleading conclusions

Tableau provides several ways to combine and evaluate data. Choosing the correct method is essential for both accuracy and performance.

flowchart LR
A[Raw Tables] --> B[Data Connection]
B --> C[Data Modeling]
C --> D[Calculations]
D --> E[Filters and Parameters]
E --> F[Visual Analysis]
F --> G[Interactive Dashboard]

Dataset Used in This Session

For this session, we use a multi-table retail dataset consisting of:

  • customers.csv
  • customer_addresses.csv
  • orders.csv and quarterly order files
  • order_details.csv
  • products.csv
  • returns.csv
  • salespeople.csv

This dataset represents a real-world analytical scenario where multiple tables must be combined to answer business questions.

Connecting to Databases

Beyond Excel and CSV files, Tableau can connect directly to relational databases.

Common enterprise sources include:

  • PostgreSQL
  • Microsoft SQL Server
  • MySQL
  • Snowflake
  • Oracl
  • Google BigQuery

Tableau Desktop and Tableau Cloud support these connectors. Tableau Public does not support direct database connections.

Why Use Database Connections

Connecting Tableau to a database allows you to:

  • Query data without exporting it into flat files
  • Reuse existing warehouse tables, views, and SQL logic
  • Refresh dashboards with current data
  • Support multi-table analysis
  • Push heavy computation to the database engine
  • Improve governance and consistency in reporting
Note

For production dashboards, direct database connectivity usually provides better scalability and better control over data freshness than manually exported files.

Tableau Desktop Connectors to SQL Server

Tableau Desktop can connect directly to Microsoft SQL Server for both exploratory analysis and production reporting.

SQL Server is widely used because it supports:

  • Large relational schemas
  • Strong authentication options
  • Views and stored procedures
  • Live querying and extract workflows
  • Enterprise-grade data governance

Why Use a SQL Server Connector

A SQL Server connection is useful when you want to:

  • Query large datasets directly
  • Build dashboards on top of trusted data models
  • Reuse business logic defined in SQL views
  • Control performance using indexing and database optimization
  • Reduce duplication of transformation logic in Tableau

Connection Types: Live vs Extract

Tableau supports two main connection modes:

  • Live connection
  • Extract connection

Live Connection

A live connection queries the underlying database every time the user interacts with the dashboard.

Use it when:

  • Data must be up-to-date
  • The database is well optimized
  • Dashboard queries are not excessively heavy
  • Freshness is more important than speed

Considerations:

  • Performance depends on database speed, network, and query complexity
  • Complex dashboards may become slow
  • Inefficient data models can create long load times

Live Connection Schema

Extract Connection

An extract copies data into Tableau’s .hyper format.

Use it when:

  • Dashboard performance is critical
  • Data does not need real-time updates
  • Dashboards contain complex calculations
  • Many users will access the dashboard

Considerations:

  • Data can become outdated between refreshes
  • Refresh schedules must be managed
  • Extract design should align with upstream refresh timing

Extract Connection
Tip

For exploratory work and many production dashboards, extracts are often the best choice because they improve speed and reduce pressure on the source database.

Step-by-Step: Connect Tableau to SQL Server

  1. Open Tableau Desktop
  2. Under Connect, choose Microsoft SQL Server
  3. Enter the connection details:
  • Server: SQL Server hostname or IP
  • Database: target database name (optional at first)
  • Authentication:
    • Windows (recommended in corporate environments)
    • SQL Server (username/password)
  1. Click Sign In
  2. After connecting, use the Data Source page to choose tables, views, or custom SQL

Connecting To SQL Server

Choosing the Right Objects: Tables, Views, or Custom SQL

When connecting Tableau to a database, you can work with several object types.

Tables

Use raw tables when:

  • You need maximum flexibility
  • You understand the grain of the data
  • You will model logic directly in Tableau

Risks:

  • Incorrect joins
  • Duplicated rows
  • Repeated business logic

Views

Views are often better when:

  • Business logic is already defined in SQL
  • KPI definitions must be standardized
  • Tableau users should not manage underlying complexity

Advantages:

  • Cleaner Tableau layer
  • Better governance
  • Less repeated logic

Custom SQL

Custom SQL allows you to write your own SQL query directly in Tableau.

Use it when:

  • You need a targeted query
  • You want to pre-aggregate or reshape data
  • You need logic that is easier to express in SQL than in Tableau

Caution:

  • Overly complex custom SQL can hurt performance
  • It may be harder to maintain than trusted warehouse views

Stored Procedures

Stored procedures can be useful when:

  • The result set is pre-optimized
  • Expensive computation already exists in SQL
  • Controlled logic is required

Behavior depends on how the procedure returns data.

Connecting To a Database
Note

In enterprise environments, trusted views are usually preferable to raw tables because they centralize business logic and reduce inconsistencies across dashboards.

Example: Simple SQL View for Tableau

The following example shows a warehouse view that prepares clean sales data for Tableau.

CREATE VIEW dbo.vw_sales_summary AS
SELECT
    o.order_id,
    o.order_date,
    c.customer_id,
    c.region,
    p.category,
    od.quantity,
    od.unit_price,
    od.quantity * od.unit_price AS line_revenue
FROM dbo.orders o
JOIN dbo.order_details od
    ON o.order_id = od.order_id
JOIN dbo.customers c
    ON o.customer_id = c.customer_id
JOIN dbo.products p
    ON od.product_id = p.product_id;

Creating Extracts

Extracts can be created in Tableau Desktop or on the web. Tableau copies data from the remote source into an optimized local structure (.hyper file), which is stored on disk either locally or on Tableau Server or Tableau Cloud.

Creating an Extract

Extract Saved Locally

When creating an extract, Tableau allows you to control:

  • Storage structure
  • Filters
  • Aggregation
  • Sampling
  • Incremental refresh settings
Tip

It is better to finalize the data model before creating an extract. Structural changes later may invalidate the extract and require rebuilding it.

Extract Properties

When creating an extract, you can configure:

  • How the extract stores data
  • Which rows are included
  • Whether data is aggregated
  • Whether incremental refresh is used

Extract Properties

Decide How the Extract Data Should Be Stored

You can choose between:

  • Logical Tables (denormalized schema)
  • Physical Tables (normalized schema)

Logical Tables

Logical tables store one extract table per logical table in the data model.

This works well when:

  • Relationships are used
  • Tables should remain conceptually separate
  • Tableau should resolve combinations dynamically

Physical Tables

Physical tables store one extract table per physical table.

This can be useful when:

  • Equality joins are used
  • You want tighter control over storage
  • You want to reduce extract size in some scenarios

Extract Filters

Extract filters limit the data before it is stored in the extract.

Use them to:

  • Reduce file size
  • Improve performance
  • Limit historical range

Aggregate Data for Visible Dimensions

This option aggregates measures at the visible dimension level.

Benefits:

  • Fewer rows
  • Smaller extract size
  • Better performance

Choose the Rows to Extract

You can select:

  • All rows
  • Top N rows where supported

Tableau applies filters and aggregation before sampling.

Incremental Refresh

Incremental refresh adds only new rows since the previous refresh.

Benefits:

  • Faster refreshes
  • Less load on source systems

Risk:

  • Schema changes usually require a full refresh
Note

If the structure of the source data changes (e.g., a new column is added), you must do a full refresh before incremental refresh can resume.

Date/Time Precision Consideration

The Tableau data engine stores time values with precision up to 3 decimal places.

If your database uses higher precision, incremental refresh can create duplicates.

Example:

  • Database rows:
    • 2015-03-13 17:30:56.502352
    • 2015-03-13 17:30:56.502852
  • Tableau stores both as:
    • 2015-03-13 17:30:56.502

This can result in duplicate rows after refresh.

Tableau Data Model

Tableau separates data modeling into two layers:

  • Logical layer
  • Physical layer

Data Model

Logical Layer

The logical layer uses relationships.

Characteristics:

  • Tables remain separate
  • Tableau combines them at query time based on the view
  • Reduces duplication risk
  • Recommended for most analytical models

Physical Layer

The physical layer uses joins and unions.

Characteristics:

  • Tables are merged into a single physical structure
  • Behavior is fixed
  • Row duplication risk is higher
  • Useful when exact row-level structure is required

Relationships vs Joins vs Unions vs Blending

Relationships

Relationships connect tables logically rather than physically.

Use relationships when:

  • Queries are done based on the view
  • Fact and dimension tables should remain separate
  • One-to-many or many-to-many structures exist

Benefits:

  • More flexible querying
  • Lower duplication risk
  • Better default behavior for analytics

Relationship Schema

Relationship

Steps to Create Relationships (Based on Example)

  1. Open the Data Source page
  2. Drag orders.csv to the canvas
  3. Drag order_details.csv next to it
  4. Tableau creates a relationship line between tables
  5. Click on the relationship line
  6. Set condition:
    • Order ID = OrderID (order_details)
  7. Validate that no duplication appears in aggregated view

Joins

Joins combine tables horizontally using matching keys.

Common join types:

  • Inner Join returns only records that have matching keys in both tables, excluding any rows that do not exist on both sides.
  • Left Join keeps all records from the left table and adds matching data from the right table, inserting NULL values when no match exists.
  • Right Join keeps all records from the right table and adds matching data from the left table, inserting NULL values when no match exists.
  • Full Outer Join keeps all records from both tables, returning matched rows where possible and NULL values where matches are missing.

Use joins when:

  • You need a fixed row-level structure
  • Tables share a clear grain

Risks:

  • Duplicated rows if grains do not match
  • Inflated aggregations

Join Schema

Join

Steps to Create Joins (Based on Example)

  1. Double-click orders.csv to enter physical layer
  2. Drag order_details.csv into the canvas
  3. Choose Left Join
  4. Set join condition:
    • Order ID = OrderID
  5. Review preview → notice duplicated rows due to multiple order lines
  6. Validate aggregation issues using SUM(Order Total)

Unions

Unions stack tables vertically.

Use unions when:

  • Tables have the same structure
  • Files are split by month, year, or source

Rules:

  • Columns should align by name and type
  • Missing columns create NULLs

Union

Steps to Create Manual Union (Specific)

  1. Drag orders_2023_Q1.csv to canvas
  2. Drag orders_2023_Q2.csv below it
  3. Repeat for all quarterly files
  4. Tableau creates a union step-by-step
  5. Click the union object to review included tables
  6. Validate column consistency and data types

Steps to Create Wildcard Union (Automatic)

  1. Drag one file (e.g., orders_2023_Q1.csv) to canvas
  2. Click the dropdown → choose New Union
  3. Select Wildcard (automatic)
  4. Define pattern:
    • Example: orders_*.csv
  5. Tableau automatically includes all matching files
  6. Validate that all expected files are included
  7. Check for schema consistency across files

Blending

Blending combines multiple data sources at the visualization level.

Characteristics:

  • One primary source
  • One or more secondary sources
  • Aggregation happens before linking

Use blending when:

  • Multiple independent data sources must appear in one view
  • A direct relational model is not available

Blend

Steps to Create Blending (Based on Example)

  1. Connect to orders.csv as primary source
  2. Add returns.csv as secondary source
  3. Open a worksheet
  4. Drag Order ID from primary
  5. Drag Order ID from secondary
  6. Ensure linking icon appears
  7. Observe NULL values where no match exists

Comparison Summary

Method Layer Adds Rows Adds Columns Multiple Sources
Relationship Logical No No No
Join Physical No Yes No
Union Physical Yes No No
Blend Visualization level No No Yes

Relationship Join

Note: When using joins, the same row may appear multiple times.
This can inflate aggregated values such as SUM([Order Total]).

Final Data Model for further analysis

Final Data Model |

Final Data Model Explanation

The final data model is built using relationships in the logical layer, not joins.

The central table in this model is orders.csv, which acts as the fact table. All other tables are connected to it or through it using appropriate keys.

This structure allows Tableau to dynamically determine how tables should be combined based on the visualization.

Why Relationships Are Used

Relationships are preferred in this model because the dataset contains tables with different levels of granularity.

For example:

  • orders.csv contains one row per order
  • order_details.csv contains multiple rows per order (one row per product in the order)

If these tables were joined directly:

  • Each order would be duplicated for every product line
  • Measures such as Order Total would be repeated
  • Aggregations like SUM(Order Total) would become incorrect

Relationships solve this problem by:

  • Keeping tables logically separate
  • Aggregating data at the correct level before combining
  • Preventing duplication and inflated results

How Tables Are Connected

The relationships in the final model are defined as follows:

  • orders.csv ↔︎ customers.csv
    • Customer ID = Customer ID
  • customers.csv ↔︎ customer_addresses.csv
    • Customer ID = Customer ID
  • orders.csv ↔︎ order_details.csv
    • Order ID = Order ID
  • order_details.csv ↔︎ products.csv
    • Product ID = Product ID
  • orders.csv ↔︎ returns.csv
    • Order ID = Order ID
  • orders.csv ↔︎ salespeople.csv
    • Salesperson ID = Salesperson ID

Important Behavior of Relationships

Unlike joins, relationships do not merge tables into a single dataset.

Instead:

  • Tableau queries each table separately
  • Combines results only when needed for the visualization
  • Adapts aggregation depending on the fields used in the view

flowchart LR
A[Orders - Fact Table] --> B[Customers]
A --> C[Order Details]
C --> D[Products]
A --> E[Returns]
A --> F[Salespeople]
B --> G[Customer Addresses]

Filters and Order of Operations

Tableau applies filters in a specific sequence. This order affects both performance and results.

The simplified order discussed in this session is:

  1. Extract Filters
  2. Data Source Filters
  3. Context Filters
  4. Dimension Filters
  5. Measure Filters

Table calculations are applied later and depend on the visible data.

flowchart TD
A[Extract Filters] --> B[Data Source Filters]
B --> C[Context Filters]
C --> D[Dimension Filters]
D --> E[Measure Filters]
E --> F[Table Calculations]
F --> G[Rendered View]

Extract Filters

Extract filters limit data before it is stored in Tableau.

Use them to:

  • Reduce volume
  • Improve performance
  • Restrict historical scope

Steps:

  • Go to Data Source tab
  • Click Extract → Edit
  • Under Filters click Add
  • Select a field (e.g. State)
  • Choose values or exclude values
  • Click OK and refresh extract

Types:

  • Dimension-based (e.g. State, City)
  • Measure-based (aggregated filtering before extract)
  • Date-based (relative or range filtering)

Extract Filter

Data Source Filters

Data source filters restrict the data available to all sheets using that data source.

Use them when:

  • A published source must be globally restricted
  • Sensitive or irrelevant data should be hidden

Steps:

  • Go to Data Source tab
  • Click Filters (top right)
  • Click Add
  • Select a field (e.g. City)
  • Define filter logic (include/exclude values)
  • Click OK

Types:

  • Dimension filters (categorical restriction)
  • Measure filters (aggregated restriction)
  • Extract filters (applied at extract level but part of data source setup)

Data Source Filter

Context Filters

Context filters create the primary subset used by later filters.

Use them when:

  • You need dependent filters
  • You want a FIXED LOD to respect a filter
  • You want performance improvements in some complex cases

Steps:

  • Add a filter to Filters shelf
  • Right-click the filter
  • Select Add to Context
  • Filter turns grey (context applied)

Context Filter

Dimension Filters

Dimension filters limit categorical values such as:

  • Region
  • Category
  • Customer name

Steps:

  • Drag a dimension (e.g. State) to Filters shelf
  • Choose filter type:
    • General (select values)
    • Wildcard (pattern match)
    • Condition (formula-based)
    • Top (ranking)
  • Select or exclude values
  • Click OK

Filter modes (from screenshot):

  • Select from list
  • Custom value list
  • Use all
  • Include / Exclude

Dimension Filter

Measure Filters

Measure filters limit aggregated numeric values such as:

  • SUM([Sales])
  • AVG([Discount])
  • COUNTD([Customer ID])

Steps:

  • Drag a measure to Filters shelf
  • Choose aggregation (SUM, AVG, COUNTD etc.)
  • Select filter type

Types:

  • Range of values (min–max slider)
  • At least (greater than or equal)
  • At most (less than or equal)
  • Special (null/non-null handling)

Context Filter

Date Filters

Date filters allow time-based slicing such as:

  • Last quarter
  • Between two dates
  • Relative periods

Steps:

  • Drag date field to Filters shelf
  • Choose filter type

Types:

  • Relative date (e.g. last 1 quarter)
  • Range of dates
  • Starting date
  • Ending date
  • Discrete date parts (year, month, quarter)

Date Filter

Top N Filters

Top N filters allow ranking by a chosen measure.

Example use cases:

  • Top 10 customers by revenue
  • Bottom 5 products by quantity

Steps:

  • Drag dimension (e.g. Customer ID) to Filters shelf
  • Go to Top tab
  • Select By field
  • Define Top N (e.g. Top 10 by COUNT(Order ID))
  • Click OK

Date Filter

Interactive Filters

Interactive filters allow dashboard users to control the visible data.

Common UI forms include:

  • Single value dropdown
  • Multiple values list
  • Slider
  • Wildcard match

Steps:

  • Right-click a filter on Filters shelf
  • Click Show Filter
  • Choose display type:
    • Single value (list/dropdown/slider)
    • Multiple values (list/dropdown/custom list)
    • Wildcard match

Show Filter

Filter UI

Advanced Chart Types

This session covers several advanced chart types commonly used in analytical dashboards.

Charts include:

  • Area chart
  • Stacked bar chart
  • Bullet chart
  • Bar-in-bar chart
  • Box plot
  • Dual-axis chart
  • Histogram
  • Heatmap
  • Highlight table
  • Treemap

Tableau Area Chart

Purpose

An area chart shows trends over time while emphasizing cumulative magnitude and contribution.

Steps to Create

  1. Drag Order Date to Columns and set it to Month

  2. Drag Customer ID to Rows and change it to Count (Distinct)

  3. Change Marks type to Area

  4. Drag City to Color

Area Chart

Best Use Cases

Use an area chart when:

  • Time is the primary axis
  • Category contribution matters
  • Trend magnitude should be emphasized

Tableau Stacked Bar Chart

Purpose

A stacked bar chart shows total size and internal composition at the same time.

Steps to Create

  1. Drag Order Date to Columns and set it to Year
  2. Drag Measure Values to Rows
  3. Drag Measure Names to Color
  4. Filter Measure Names to keep only relevant measures
  5. Set the marks type to Bar
  6. Drag Measure Values to Label (hold Ctrl).
  7. Adjust colors and bar size.

Stacked Bar Chart

Best Use Cases

Use a stacked bar chart when:

  • You need part-to-whole comparison
  • Total values must remain visible
  • Category composition is important

Tableau Bullet Chart

Purpose

A bullet chart compares actual performance against one or more benchmarks while conserving space.

Business Context

Typical usage includes:

  • Actual vs target
  • Category value vs average benchmark
  • Performance band analysis

Steps to Create

  1. Drag Category to the Rows shelf.
  2. Drag SUM([Total Quantity]) to the Columns shelf.
  3. Open Show Me and select Bullet Graph.
  4. Tableau automatically:
    • Creates the bullet bar
    • Adds a reference line based on aggregation
  5. Right-click the axis → Add Reference Line
  6. Set the reference line to:
    • Scope: Entire Table
    • Value: Average of SUM([Total Quantity])
  7. Add additional reference lines or bands using:
    • Percent of average (e.g. 60%, 80%, 100%)
  8. Adjust:
    • Bar color (actual values)
    • Band opacity (performance ranges)
    • Tooltip text for clarity

Bullet Chart

In this example:

  • Blue bars represent the actual total quantity per Category
  • Background bands represent performance ranges based on the overall average
  • Vertical reference lines indicate benchmark values (e.g. average or percentage of average)

This allows quick identification of: - Categories performing below average - Categories performing near or above benchmark (e.g. 60% of Average Total Quantity = 2,874, as shown in the tooltip) - Relative performance without comparing categories directly

Best Use Cases

Use a bullet chart when:

  • You need compact benchmark comparison
  • Dashboards have limited space
  • Performance against a target matters

Tableau Bar-In-Bar Chart

Purpose

A bar-in-bar chart compares two related measures for the same dimension.

Method 1: Shared Axis

Use when:

  • Measures are on a similar scale
  • One measure is conceptually part of the other

Method 2: Separate Axes

Use when:

  • Measures differ significantly in size
  • Visibility is more important than strict proportionality

Bar In Bar Chart:Method 1 (Shared Axis)

Bar In Bar Chart:Method 2 (Separate Axis)

Tableau Box Plot

Purpose

A box plot shows the distribution of a measure, including spread, center, and outliers.

What It Shows

For each category, a box plot reveals:

  • Median
  • Interquartile range
  • Whiskers
  • Outliers

Steps to Create

  1. Drag MONTH([Order Date]) to the Columns shelf.
  2. Drag SUM([Order Total]) to the Rows shelf (this creates the bar chart).
  3. Drag SUM([Order Total]) to the Rows shelf a second time.
  4. On the second measure:
    • Change Marks type to Line
    • Apply a table calculation (e.g. Moving Sum or Running Total)
  5. Right-click the second axis and select Dual Axis.
  6. Synchronize axes if necessary and adjust colors.

Box and Whisker Plot

Best Use Cases

Use a box plot when:

  • Variability matters more than totals
  • You need to detect outliers
  • Category-level distributions should be compared

Tableau Dual-Axis Chart

Purpose

A dual-axis chart overlays two measures in a single view.

Example Usage

  • Bars for monthly sales
  • Line for running total or moving average

Steps to Create

  1. Drag MONTH([Order Date]) to the Columns shelf.
  2. Drag SUM([Order Total]) to the Rows shelf (this creates the bar chart).
  3. Drag SUM([Order Total]) to the Rows shelf a second time.
  4. On the second measure:
    • Change Marks type to Line
    • Apply a table calculation (e.g. Moving Sum or Running Total)
  5. Right-click the second axis and select Dual Axis.
  6. Synchronize axes if necessary and adjust colors.

Dual Axis Chart

Best Use Cases

Use a dual-axis chart when:

  • Two related measures should be analyzed together
  • Time-based trend comparison is needed
  • One measure provides context for the other

Tableau Histogram

Purpose

A histogram analyzes the distribution of a continuous numeric variable.

Steps to Create

  1. Right-click Order Total → Create → Bins
  2. Choose an appropriate bin size (based on data range and analysis goal)
  3. Drag Order Total (bin) to the Columns shelf
  4. Drag COUNT([Order Total]) to the Rows shelf
  5. Set Marks type to Bar

Histogram

Interpretation

A histogram helps answer:

  • Where most values cluster
  • Whether the distribution is symmetric or skewed
  • Whether extreme values exist

Best Use Cases

Use a histogram when:

  • Distribution shape matters
  • Numeric ranges matter more than categories
  • Outliers or skewness should be detected

Tableau Heatmap

Purpose

A heatmap compares magnitude across two categorical dimensions using color intensity.

Steps to Create

  1. Drag City to the Rows shelf.
  2. Drag YEAR([Order Date]) to the Columns shelf.
  3. Drag SUM([Order Total]) to Color on the Marks card.
  4. Set Marks type to Square (or Automatic).
  5. Adjust the color palette to:
    • Light colors → lower values
    • Dark colors → higher values

Heatmap

Best Use Cases

Use a heatmap when:

  • Pattern discovery matters
  • You need fast comparison across a matrix
  • Magnitude can be encoded visually

Tableau Highlight Table

Purpose

A highlight table combines a table structure with color encoding so that users can see both exact values and relative intensity.

Steps to Create

  1. Drag City to the Rows shelf.
  2. Drag YEAR([Order Date]) to the Columns shelf.
  3. Drag SUM([Order Total]) to Color on the Marks card.
  4. Drag SUM([Order Total]) to Label on the Marks card.
  5. Set Marks type to Square.
  6. Adjust the color palette to reflect low → high values.

Highlight Table

Highlight Table vs Heatmap

Feature Highlight Table Heatmap
Exact values visible Yes No
Pattern emphasis Yes Yes
Precision High Lower

Best Use Cases

Use a highlight table when:

  • Exact values matter
  • Color should support quick comparison
  • The view functions as both report and analysis

Tableau Treemap

Purpose

A treemap shows part-to-whole relationships across many categories using area and color.

Steps to Create

  1. Drag State to Label (or first level in hierarchy).
  2. Drag City to Label (nested under State).
  3. Drag COUNTD([Customer ID]) to Size.
  4. Drag COUNTD([Customer ID]) to Color.
  5. Set Marks type to Treemap.
  6. Drag COUNTD([Customer ID]) to Label, right-click choose quick table qalqulations → percent of total.

Treemap

Best Use Cases

Use a treemap when:

  • There are many categories
  • Relative contribution matters
  • Exact comparison is not the primary goal

Calculated Fields

Calculated fields allow you to create new logic from existing data.

They are one of Tableau’s most important analytical features.

Calculated fields can be used to:

  • Create new metrics
  • Transform data
  • Segment records
  • Build KPI logic
  • Control dashboard behavior

Creating a Calculated Field

  1. Go to Analysis → Create Calculated Field
  2. Enter a name
  3. Write the formula
  4. Validate and save

Calculated Field

Calculation Evaluation Flow

Tableau calculations are evaluated at different stages.

flowchart LR
A[Raw Data Rows] --> B[Row-Level Calculations]
B --> C[Aggregation Based on View]
C --> D[Aggregate Calculations]
D --> E[LOD Override if Defined]
E --> F[Visualization]

Row-Level Calculations

Row-level calculations operate on each individual record before aggregation.

Use row-level calculations when you need to:

  • Create line-level revenue
  • Standardize raw fields
  • Build record-level flags
  • Classify rows

Example: Order Line Revenue

[Quantity] * [Unit Price]

Each row calculates its own revenue value.

Revenue calculation

Revenue

Example: Revenue Banding

IF [Quantity] * [Unit Price] >= 1000 THEN "High Value"
ELSE "Standard"
END

Aggregate Calculations

Aggregate calculations operate after Tableau groups data according to the dimensions in the view.

Common aggregate functions include:

  • SUM()
  • AVG()
  • COUNT()
  • COUNTD()
  • MIN()
  • MAX()

Example: Average Order Value

SUM([Order Total]) / COUNTD([Order ID])

This formula returns average revenue per order at the current level of aggregation.

Average Order Value
Important

Tableau does not allow incorrect mixing of row-level and aggregate fields in the same expression. For example, this is invalid:

SUM([Sales]) / [ORDER ID]

A corrected version would aggregate both sides:

SUM([Sales]) / COUNT([ORDER ID])

Level of Detail (LOD) Expressions

LOD expressions explicitly control the granularity of a calculation independently of the view.

There are three main types:

  • FIXED
  • INCLUDE
  • EXCLUDE

flowchart TD
A[View Level of Detail] --> B[Default Aggregation]
B --> C[LOD Expression]
C --> D[FIXED]
C --> E[INCLUDE]
C --> F[EXCLUDE]

FIXED

FIXED calculates at a specified level regardless of view dimensions.

Example: Customer Lifetime Value

{ FIXED [Customer ID] : SUM([Order Total]) }

Use FIXED when:

  • A metric must remain stable across sheets
  • A KPI should be defined at a business entity level
  • The calculation should ignore most view-level dimensional changes

Customer Lifetime Value

Example: Minimum Order Date per Customer

{ FIXED [Customer ID] : MIN([Order Date]) }

This calculation returns the first purchase date for each customer.

Minimum Order Date per Customer

INCLUDE

INCLUDE adds detail to the calculation level even if that dimension is not in the view.

Example:

{ INCLUDE [Region] : AVG([Order Total]) }

Use INCLUDE when:

  • Extra detail should influence the result
  • The view is less granular than the analytical logic

Average Sales by Region-Year

EXCLUDE

EXCLUDE removes a dimension from the calculation level even if it appears in the view.

Example:

{ EXCLUDE [Region] : AVG([Order Total]) }

Use EXCLUDE when:

  • A dimension is needed visually
  • But should not affect the aggregation logic

Even if Region appears in the view, this calculation computes the average as if the Region dimension did not exist.

Average Sales by Region/Exclude

Comparison Example

  • AVG([Order Total])
    → Calculates average sales per Region and Year

  • { INCLUDE [Region] : AVG([Order Total]) }
    → Calculates average sales per Region, then aggregates to Year

  • { EXCLUDE [Region] : AVG([Order Total]) }
    → Calculates average sales per Year ignoring Region

  • { FIXED [Region] : AVG([Order Total]) }
    → Calculates average sales per Region regardless of Year

Differences of LOD expressions
Important

LOD expressions are essential for building consistent KPIs, customer-level metrics, cohort logic, and benchmark calculations.

Tableau Functions

Tableau provides many built-in functions that can be used in calculated fields.

Main categories include:

  • Aggregate functions
  • Number functions
  • String functions
  • Logical functions
  • Type conversion functions

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

Examples:

  • SUM([Order Total]) → total revenue from orders dataset
  • AVG([Unit Price]) → average product price from products.csv
  • COUNT([Order ID]) → total number of orders
  • COUNTD([Customer ID]) → number of unique customers

Use case with dataset:

  • Calculate total sales:
SUM([Order Total])
  • Count unique customers placing orders:
COUNTD([Customer ID])

Number Functions

Number functions perform mathematical operations on numeric fields.

Examples:

  • ROUND([Order Total], 2) → rounds order value to 2 decimal places
  • ABS([Discount]) → removes negative sign from discount values
  • CEILING([Unit Price]) → rounds price up to nearest integer
  • FLOOR([Unit Price]) → rounds price down

Use case with dataset:

  • Round order totals:
ROUND([Order Total], 0)
  • Convert negative discounts to positive values:
ABS([Discount])

String Functions

String functions manipulate text fields.

Examples:

  • UPPER([Customer Name]) → converts names to uppercase
  • LOWER([City]) → converts city names to lowercase
  • LEFT([Product ID], 3) → extracts first 3 characters of product ID
  • TRIM([Customer Name]) → removes extra spaces

Use case with dataset:

  • Standardize city names:
UPPER([City])
  • Extract product category prefix:
LEFT([Product ID], 3)

Logical Functions

Logical functions apply conditional logic to create categories or flags.

Use case with dataset:

  • Segment orders based on value:
IF [Order Total] >= 500 THEN "Large Order"
ELSE "Small Order"
END
  • Categorize products:
CASE [Category]
WHEN "Beauty" THEN "Personal Care"
WHEN "Clothing" THEN "Apparel"
ELSE "Other"
END

Type Conversion Functions

Type conversion functions change data types between string, number, and date.

Examples:

  • STR([Customer ID]) → convert numeric ID to text
  • INT([Unit Price]) → convert price to integer
  • DATE([Order Date]) → ensure field is treated as date
  • FLOAT([Discount]) → convert value to decimal

Use case with dataset:

  • Convert Customer ID to string for concatenation:
STR([Customer ID])
  • Convert price to integer:
INT([Unit Price])

Date functions and spatial functions will be explored in the next session.

Parameters

A parameter is a workbook variable that can store a value chosen by the user.

Parameters can be used in:

  • Calculations
  • Filters
  • Reference lines
  • Axis logic

Key Characteristics

Parameters:

  • Do not filter data automatically
  • Must be referenced in logic
  • Do not follow filter order of operations
  • Can store numbers, strings, dates, or booleans

flowchart LR
A[Parameter Value] --> B[Calculated Field]
B --> C[View Logic]
C --> D[Displayed Result]

To Use a Parameter

  1. Create the parameter
  2. Use it in a calculated field, filter, or reference line
  3. Show the parameter control to the user

Step-by-step

  • In the Data pane, click the dropdown menu
  • Select Create Parameter…
  • Configure:
    • Name
    • Data type
    • Allowable values (List / Range / All)
  • Click OK
  • Right-click the parameter → Show Parameter

Create Parameter

Metric Switching

A parameter can let users switch between metrics.

Example parameter-driven calculation:

CASE [Metric]
WHEN 1 THEN SUM([Revenue])
WHEN 2 THEN SUM([Quantity])
ELSE SUM([Profit])
END

Steps

  • Create parameter:
    • Name: Metric
    • Data type: Integer
    • Values:
      • 1 → Revenue
      • 2 → Quantity
  • Create calculated field:
CASE [Metric]
WHEN 1 THEN SUM([Revenue])
ELSE SUM([Quantity])
END
  • Drag the calculated field into the view
  • Right-click parameter → Show Parameter
  • Use dropdown to switch metrics

Parameter Properties

Metric Swapping Calculation
Note

Name of the calculated field should be identical to the parameter name and can include p. in the name to make it clear that it is a calculation for the parameter.

Dimension Switching

A parameter can switch dimensions in the view.

Example:

CASE [Location Category]
WHEN 'State' THEN [State]
ELSE [City]
END

Steps

  • Create parameter:
    • Name: Location Category
    • Data type: String
    • Values: State, City
  • Create calculated field:
CASE [Location Category]
WHEN 'State' THEN [State]
ELSE [City]
END
  • Drag calculated field to Rows or Columns
  • Show parameter
  • Switch between State and City

Dimension Swapping

Dimension Swapping Calculation

Boolean Parameter Logic

A boolean-style parameter can toggle analysis behavior.

Example:

IF [Is Florida] THEN
    [State] = 'FL'
ELSE
    [State] <> 'FL'
END

Steps

  • Create parameter:
    • Name: Is Florida
    • Data type: Boolean
  • Create calculated field:
IF [Is Florida] THEN
    [State] = 'FL'
ELSE
    [State] <> 'FL'
END
  • Drag calculation to Filters
  • Select True
  • Show parameter to toggle

Boolean Parameter

Boolean Parameter Calculation

Top N with Parameter

A numeric parameter can drive Top N analysis.

Common workflow:

  • Create parameter Top Values
  • Use it in a Top tab filter
  • Let the user choose how many values to show

Steps

  • Create parameter:
    • Name: Top values
    • Data type: Integer
  • Drag dimension (Customer Name) to Filters
  • Go to Top tab
  • Select:
    • By field
    • Top [Top values] by SUM(Revenue)
  • Show parameter

Parameter Filter

Top N Filter

Date Parameter Logic

Parameters can also support date-based filtering.

Example:

[Order Date] <= [Select Return Date]

Steps

  • Create parameter:
    • Name: Select Return Date
    • Data type: Date
    • Add values from Return Date
  • Create calculated field:
[Order Date] <= [Select Return Date]
  • Drag calculation to Filters
  • Keep True
  • Show parameter

Date Filtering

Date Filtering Calculation
Tip

You can set the default value of the date parameter to a dynamic value such as:

  • TODAY()
  • MAX([ORDER DATE])

Set When workbook opens → MAX(Order Date) to always show the latest data.

Filters, Parameters, and Calculated Fields

Understanding how filters and parameters affect calculations is essential for correct results.

Dimension Filters

Dimension filters are applied after FIXED LOD expressions but before table calculations.

Implications:

  • They do not affect FIXED by default
  • They do affect INCLUDE and EXCLUDE
  • They affect table calculations because they change the visible data

Example:

{ FIXED [Customer ID] : SUM([Sales]) }

If you add a regular dimension filter on [Region], the FIXED result does not automatically change.

Measure Filters

Measure filters are applied after aggregation.

They affect:

  • Aggregated results
  • Visible marks
  • Table calculations

They do not recompute FIXED LOD expressions.

Context Filters

Context filters change the evaluation path by creating a subset earlier in the pipeline.

Implications:

  • Context filters do affect FIXED
  • They can improve performance in some cases
  • They support dependent filters

Example:

{ FIXED [Customer ID] : SUM([Sales]) }

If [Region] is placed in context, the calculation is now evaluated only for the selected region.

Table Calculations

Table calculations are applied after most filters and depend on the displayed marks.

They are influenced by:

  • Sorting
  • Layout
  • Partitioning
  • Addressing
  • Visible data

flowchart TD
A[Source Data] --> B[LOD and Aggregations]
B --> C[Filters]
C --> D[Visible Marks]
D --> E[Table Calculations]

Note

Parameters change logic only when they are explicitly referenced. Filters change the actual subset of data being evaluated.

Example: Comparing SQL Logic with Tableau Logic

Some logic can be prepared in SQL before Tableau, while other logic can be built directly inside Tableau.

SQL Example: Precompute Revenue

SELECT
    order_id,
    quantity,
    unit_price,
    quantity * unit_price AS revenue
FROM dbo.order_details;

Tableau Example: Same Logic in a Calculated Field

[Quantity] * [Unit Price]

When to Prefer SQL

Prefer SQL when:

  • Logic should be standardized
  • Data should be reshaped before Tableau
  • Performance depends on pre-aggregation

When to Prefer Tableau

Prefer Tableau when:

  • Logic is view-specific
  • Analysts need flexibility
  • Interactivity or parameterization is required