Replicating Tableau Table Calculations in SQL to Diagnose Data Quality Issues

sql

tableau

data quality

When a stakeholder says the numbers don’t look right, you need a method to determine whether the problem lives upstream in the database or inside Tableau itself. I replicate eight Superstore table calculations in BigQuery, step by step, and show real cases where SQL caught Tableau misconfigurations.

Author

Gendson Moreira

Published

April 15, 2026

Background

In practice, analytics teams spend more time validating dashboards than building them. Between inherited logic, shifting metric definitions, and pipeline changes, “the numbers look off” from a stakeholder lands without any obvious starting point.

This post builds on a framework shared by Sebastine on LinkedIn for diagnosing dashboard discrepancies. His final step, verify directly from the datasource, is the one I want to expand on here with concrete SQL.

The idea: rebuild the Tableau view as a SQL query and compare the results.

If the numbers match → the issue is upstream. The Tableau logic is correct. Something changed in the data pipeline. Talk to your data engineer.
If the numbers don’t match → the issue is inside Tableau. Calculations, filters, or LOD interactions are the culprit. You know exactly where to focus.

Throughout the post, I’m using the Superstore dataset loaded into BigQuery. I replicate eight table calculations (from running totals to percentiles) and walk through the full diagnostic comparison for each.

Note

All queries in this post are written in BigQuery SQL, the most widely adopted cloud data warehouse dialect in the market. The concepts translate directly to Snowflake, PostgreSQL, and Redshift with minor syntax adjustments.

Setup: Superstore in BigQuery

The Superstore dataset is publicly available on Kaggle. Download Sample - Superstore.csv and upload it to BigQuery.

Important

Column names in Superstore contain spaces (Order Date, Customer Name) and hyphens (Sub-Category). In BigQuery, always wrap these in backticks: `Order Date`. Single-word columns like Profit and Sales do not need backticks.

A Note on CTEs

Every query in this post uses a CTE (Common Table Expression), defined with WITH name AS (...) before the main SELECT. A CTE is a temporary named result set that only exists for the duration of the query.

The reason every query here needs one: window functions can’t run on top of aggregate functions in the same query level. BigQuery requires the aggregation to resolve first, then the outer SELECT applies the window function. Tableau works the same way internally: aggregate first, table calculation on top.

RAW ORDERS TABLE      →    CTE (aggregate)     →    Outer SELECT (window function)
9,994 rows                 one row per group         running total / diff applied

The Dashboard

All eight calculations below are live in this Tableau Public dashboard. Use it as a reference alongside the SQL queries.

Viz 1: Running Total

What Tableau is doing

The running total is a Table Calculation computed after aggregation, on the result set Tableau has already built. The formula is:

RUNNING_SUM(SUM([Profit]))

In this dashboard, the calculation is partitioned by Year and ordered by Quarter. It accumulates Q1 through Q4 within each year and resets at the start of the next.

Unlike a simple SUM(), a running total is positional, not set-based. That’s why SQL needs a window function to replicate it.

SQL equivalent

WITH
  profit_by_quarter AS (
    SELECT
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      EXTRACT(QUARTER FROM `Order Date`) AS order_quarter,
      SUM(Profit) AS quarterly_profit
    FROM `your_project.superstore.orders`
    GROUP BY
      order_year,
      order_quarter
  )
SELECT
  order_year,
  order_quarter,
  quarterly_profit,
  SUM(quarterly_profit)
    OVER (
      PARTITION BY order_year
      ORDER BY order_quarter
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_profit
FROM profit_by_quarter
ORDER BY order_year, order_quarter

Tip

Why PARTITION BY order_year is the critical detail: without it, the running total would accumulate across all years. 2015 Q1 would carry over 2014’s full total instead of resetting. In Tableau, this is controlled by the “Restart Every” setting in the table calculation dialog, which defines which dimension resets the calculation back to zero. PARTITION BY in SQL is the exact equivalent: it sets the boundary where the window function starts over. This is not limited to date fields: you can restart by Category, Region, or any other dimension. The partition dimension determines the scope of each independent calculation window.

Viz 2: Difference From Previous

What Tableau is doing

Each bar shows how much profit changed from the prior month, calculated with DIFFERENCE(SUM([Profit]), -1). The calculation is partitioned by Category (each panel is independent) and ordered by Month within Year. January always looks back to the December immediately before it, even if that December belongs to the prior year.

The nuance here: filter scope directly affects what “previous” means. Cut out the month immediately before the first visible month, and Tableau loses its reference point and returns wrong values.

SQL equivalent

WITH
  monthly_profit_by_category AS (
    SELECT
      Category AS product_category,
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      EXTRACT(MONTH FROM `Order Date`) AS order_month,
      SUM(Profit) AS total_profit_in_month
    FROM `your_project.superstore.orders`
    WHERE `Order Date` >= '2016-12-01'
    GROUP BY
      Category,
      EXTRACT(YEAR FROM `Order Date`),
      EXTRACT(MONTH FROM `Order Date`)
  ),
  profit_with_lag AS (
    SELECT
      product_category,
      order_year,
      order_month,
      total_profit_in_month,
      LAG(total_profit_in_month, 1)
        OVER (
          PARTITION BY product_category
          ORDER BY order_year, order_month
        ) AS previous_month_profit
    FROM monthly_profit_by_category
  )
SELECT
  product_category,
  order_year,
  order_month,
  total_profit_in_month,
  previous_month_profit,
  total_profit_in_month
    - previous_month_profit AS diff_from_previous_month
FROM profit_with_lag
ORDER BY product_category, order_year, order_month

Why December 2016 belongs in the CTE but not the output

Including December 2016 in the CTE but removing it from the final SELECT works the same way a context filter does in Tableau: it shapes what the calculation sees without appearing in the output.

CTE WHERE clause        →   includes Dec 2016   →   LAG() has a reference point
Final SELECT WHERE      →   excludes Dec 2016   →   output shows only 2017 months

Move the WHERE order_year = 2017 into the CTE instead of the final SELECT, and January 2017 returns NULL for previous_month_profit. There’s no December 2016 row left for LAG() to look back at. Filter order matters here exactly as it does in Tableau.

Tip

Filter placement rule: a WHERE clause in the first CTE replicates a Tableau context filter, applied before the window function sees the data. A WHERE clause in the final SELECT replicates a Tableau table calculation filter, the only filter type that runs after the table calculation has already computed, leaving the partition intact during computation. Same filter, completely different results. See Tableau’s Order of Operations.

Viz 3: Percent Difference From Previous

What Tableau is doing

Month-over-month sales change, expressed as a percentage. The Tableau formula is:

(SUM([Sales]) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

LOOKUP(expr, -1) pulls the previous row’s value, equivalent to LAG(1) in SQL. ZN() coerces NULL to 0, and ABS() in the denominator prevents a sign flip: without it, dividing a negative numerator by a negative denominator returns a positive percentage when it shouldn’t.

The calculation has no partition, runs across the full table ordered by month, and the filter covers 2016-12-01 → 2017-12-31. December 2016 is included only as a baseline for January 2017’s LAG value.

SQL equivalent

WITH
  monthly_sales AS (
    SELECT
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      EXTRACT(MONTH FROM `Order Date`) AS order_month,
      SUM(Sales) AS total_sales_in_month
    FROM `your_project.superstore.orders`
    WHERE `Order Date` BETWEEN '2016-12-01' AND '2017-12-31'
    GROUP BY
      EXTRACT(YEAR FROM `Order Date`),
      EXTRACT(MONTH FROM `Order Date`)
  ),
  sales_with_lag AS (
    SELECT
      order_year,
      order_month,
      total_sales_in_month,
      LAG(total_sales_in_month, 1)
        OVER (
          ORDER BY order_year, order_month
        ) AS previous_month_sales
    FROM monthly_sales
  )
SELECT
  order_year,
  order_month,
  total_sales_in_month,
  previous_month_sales,
  ROUND(
    (total_sales_in_month - previous_month_sales)
      / NULLIF(ABS(previous_month_sales), 0)
      * 100,
    1) AS pct_diff_from_previous_month
FROM sales_with_lag
WHERE order_year = 2017
ORDER BY order_year, order_month

Tip

Why ABS() in the denominator matters: if the previous month’s sales were negative (rare in Superstore but common in margin or profit views), dividing by a negative number flips the sign of the result. ABS() ensures the percentage always reflects the true direction of change (positive when sales grew, negative when they fell) regardless of the sign of the baseline.

Viz 4: Percent of Total

What Tableau is doing

Each state’s share of total sales, used to color a geographic map. The formula is:

SUM([Sales]) / TOTAL(SUM([Sales]))

TOTAL() is unusual: it ignores the current partition entirely and always computes across the full table. Even if the view is partitioned by Region, TOTAL() returns the grand total across all regions.

SQL has no built-in equivalent. You compute the grand total in a separate CTE and join it in. A cross join on a single-row aggregate does the job exactly.

SQL equivalent

WITH
  sales_by_state AS (
    SELECT
      State AS state,
      SUM(Sales) AS total_sales_in_state
    FROM `your_project.superstore.orders`
    GROUP BY State
  ),
  total_sales AS (
    SELECT
      SUM(total_sales_in_state) AS grand_total_sales
    FROM sales_by_state
  )
SELECT
  s.state,
  s.total_sales_in_state,
  t.grand_total_sales,
  ROUND(
    s.total_sales_in_state
      / t.grand_total_sales
      * 100,
    2) AS pct_of_total_sales
FROM sales_by_state s
CROSS JOIN total_sales t
ORDER BY pct_of_total_sales DESC

Tip

CROSS JOIN on a single-row CTE is the standard SQL pattern for replicating TOTAL(). Because the total_sales CTE returns exactly one row, the cross join attaches the grand total to every state row without duplicating data.

Viz 5: Rank

What Tableau is doing

Sub-categories ranked by profit ratio within each year. The rank runs on a calculated field:

Profit Ratio = SUM([Profit]) / SUM([Sales])
RANK([Profit Ratio])

Partitioned by Year, ordered by Sub-Category. A higher profit ratio means a lower rank number: rank 1 is the most profitable sub-category in that year.

SQL equivalent

WITH
  profit_ratio_by_year AS (
    SELECT
      `Sub-Category` AS sub_category,
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      SUM(Profit) AS total_profit,
      SUM(Sales) AS total_sales,
      ROUND(
        SUM(Profit) / NULLIF(SUM(Sales), 0) * 100, 2) AS profit_ratio_pct
    FROM `your_project.superstore.orders`
    GROUP BY
      `Sub-Category`,
      EXTRACT(YEAR FROM `Order Date`)
  )
SELECT
  sub_category,
  order_year,
  profit_ratio_pct,
  RANK()
    OVER (
      PARTITION BY order_year
      ORDER BY profit_ratio_pct DESC
    ) AS profit_ratio_rank
FROM profit_ratio_by_year
WHERE order_year IN (2014, 2017)
ORDER BY order_year, profit_ratio_rank

Viz 6: Percent From Previous

What Tableau is doing

Current quarter sales as a ratio of the prior quarter. Not a difference but a ratio. The Tableau formula is:

ZN(SUM([Sales])) / LOOKUP(ZN(SUM([Sales])), -1)

Unlike Viz 3, there’s no subtraction. current / previous returns above 1 when sales grew and below 1 when they fell. ZN() coerces NULL to 0. The first quarter in the dataset returns NULL, with no prior row for LOOKUP to reference.

SQL equivalent

WITH
  quarterly_sales AS (
    SELECT
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      EXTRACT(QUARTER FROM `Order Date`) AS order_quarter,
      COALESCE(SUM(Sales), 0) AS total_sales_in_quarter
    FROM `your_project.superstore.orders`
    GROUP BY
      EXTRACT(YEAR FROM `Order Date`),
      EXTRACT(QUARTER FROM `Order Date`)
  ),
  sales_with_lag AS (
    SELECT
      order_year,
      order_quarter,
      total_sales_in_quarter,
      LAG(total_sales_in_quarter, 1)
        OVER (
          ORDER BY order_year, order_quarter
        ) AS previous_quarter_sales
    FROM quarterly_sales
  )
SELECT
  order_year,
  order_quarter,
  total_sales_in_quarter,
  previous_quarter_sales,
  ROUND(
    COALESCE(total_sales_in_quarter, 0)
      / NULLIF(previous_quarter_sales, 0)
      * 100,
    1) AS pct_from_previous_quarter
FROM sales_with_lag
ORDER BY order_year, order_quarter

Viz 7: Percentile

What Tableau is doing

Orders flagged when their discount exceeds the 90th percentile across the entire dataset. This uses a Level of Detail (LOD) expression, not a table calculation:

[Discount] > { FIXED : PERCENTILE([Discount], 0.90) }

FIXED with no dimension computes the threshold across the full dataset. Each row gets a boolean: TRUE for outlier discounts, FALSE for everything else. Orders group into two bars per category: grey for normal, red for outlier.

LOD or not, the diagnostic method is the same: replicate the logic in SQL and compare counts.

SQL equivalent

WITH
  p90_threshold AS (
    SELECT
      PERCENTILE_CONT(Discount, 0.90) OVER () AS p90_discount
    FROM `your_project.superstore.orders`
    LIMIT 1
  ),
  orders_flagged AS (
    SELECT
      o.Category AS category,
      o.Discount AS discount,
      p.p90_discount,
      CASE
        WHEN o.Discount > p.p90_discount
          THEN TRUE
        ELSE FALSE
        END AS discount_exceeds_p90
    FROM `your_project.superstore.orders` o
    CROSS JOIN p90_threshold p
  )
SELECT
  category,
  COUNTIF(discount_exceeds_p90 = FALSE) AS orders_within_p90,
  COUNTIF(discount_exceeds_p90 = TRUE) AS orders_exceeding_p90,
  ROUND(p90_discount * 100, 1) AS p90_discount_threshold_pct
FROM orders_flagged
GROUP BY category, p90_discount
ORDER BY orders_within_p90 DESC

Tip

PERCENTILE_CONT in BigQuery requires OVER(), as it is only available as a window function, not a regular aggregate. OVER() with nothing inside means compute across the entire table. The LIMIT 1 is needed because without it, BigQuery returns the same threshold value once per row.

Viz 8: Moving Average

What Tableau is doing

A 3-month trailing average of sales, excluding the current month. The formula is:

WINDOW_AVG(SUM([Sales]), -3, -1)

The offsets -3 and -1 set the window bounds: start 3 rows back, stop 1 row before the current row. Tableau requires the full window. Fewer than 3 prior months returns NULL, not a partial average. The first 3 months in the dataset will always be NULL.

SQL equivalent

WITH
  monthly_sales AS (
    SELECT
      EXTRACT(YEAR FROM `Order Date`) AS order_year,
      EXTRACT(MONTH FROM `Order Date`) AS order_month,
      SUM(Sales) AS total_sales_in_month
    FROM `your_project.superstore.orders`
    GROUP BY
      EXTRACT(YEAR FROM `Order Date`),
      EXTRACT(MONTH FROM `Order Date`)
  ),
  moving_average AS (
    SELECT
      order_year,
      order_month,
      total_sales_in_month,
      ROW_NUMBER()
        OVER (
          ORDER BY order_year, order_month
        ) AS row_num,
      AVG(total_sales_in_month)
        OVER (
          ORDER BY order_year, order_month
          ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
        ) AS moving_avg_3_months
    FROM monthly_sales
  )
SELECT
  order_year,
  order_month,
  ROUND(total_sales_in_month / 1000, 1) AS current_month_sales,
  CASE
    WHEN row_num <= 3 THEN NULL
    ELSE ROUND(moving_avg_3_months / 1000, 1)
    END AS moving_avg_3_months_sales
FROM moving_average
ORDER BY order_year, order_month

Tip

BigQuery computes partial moving averages. Tableau does not. When fewer than 3 prior months exist, BigQuery’s AVG() window function returns the average of whatever rows are available (1 or 2). Tableau requires the full window and returns NULL instead. The ROW_NUMBER() guard corrects this: replace the partial average with NULL for the first N months, where N equals the window size. This pattern scales directly: a 6-month window needs row_num <= 6, a 12-month window needs row_num <= 12.

Before Concluding the Numbers Are Wrong

When SQL and Tableau disagree, the first instinct is to blame the data. But more often the query is incomplete: it’s not fully replicating what Tableau is computing. Tableau stacks filters in layers: extract, data source, context, dimension, measure, and table calculation. A gap at any one of them is enough to produce a number that looks wrong but isn’t.

Before drawing any conclusion, check whether the SQL reflects every filter active in the workbook. That means filters on the Filters shelf, any field set as a context filter, the partition and direction settings of the table calculation, and row-level security if the workbook is on Tableau Server. Any of these can quietly constrain what Tableau computes without appearing on the dashboard surface.

SQL has no implicit filters. Everything must be written explicitly. That’s what makes it useful here: a missing filter shows up as a discrepancy immediately, rather than hiding somewhere in the visual layer.

Closing Thoughts

Rebuilding a Tableau view in SQL does more than debug a number. It produces something you can hand to anyone on the team. A query that reproduces the dashboard exactly is a documented metric definition, one that any analyst or data engineer can read and verify without opening Tableau.

What makes it work is the forced precision. You can’t replicate a Tableau calculation in SQL without pinning down exactly what it computes, across what scope, and in what order. That exercise turns “the numbers look off”, a report that’s impossible to act on, into something you can actually fix.

Inspired by the dashboard validation framework originally shared by Sebastine on LinkedIn. The SQL implementations, BigQuery setup, and diagnostic case studies are original extensions built on top of that foundation.