When working with Tableau, one common challenge is getting subtotals and totals to display the correct values—especially when using distinct counts. If you’ve ever been frustrated by a subtotal that simply sums up row values rather than reflecting a true distinct count, this blog is for you. Today, we’ll explore a nifty trick using table calculations and Level of Detail (LOD) expressions to hack your way around Tableau’s default behavior.

The Challenge: Distinct Counts Gone Awry

Imagine you have a dashboard where you’re tracking opportunities by different marketing channels stages. For example, you might have:

• Totals Column:
  
  COUNTD(
  [Opportunity ID]
  )

At the row level, these calculations work fine. However, when you enable subtotals, Tableau will simply add the values of each row. This behavior can be misleading—especially if an opportunity passes through more than one marketing channel—resulting in a subtotal that is far off from what you expect. You might anticipate a total of 100 distinct opportunities, but Tableau ends up showing 200 non-distinct opportunities . What’s going on?

Why Does This Happen?

Tableau calculates row-level values independently. When you add up these rows, the default subtotal is the sum of those values. With COUNTD, summing distinct counts doesn’t automatically yield the union of distinct values across groups—it just adds each group's count, potentially double-counting or even undercounting if the table calculation isn’t partitioned correctly.

The Window-Sum Trick: Forcing the Correct Subtotal

A clever workaround involves using a combination of an LOD expression and a table calculation. The trick is to detect when Tableau is calculating the subtotal (or grand total) and then override the default behavior. Here’s the idea in a nutshell:

1. At the Detail Level:
   • Use your standard distinct count calculation.
2. At the Subtotal/Grand Total Level:
   • Use a WINDOW_SUM around a FIXED LOD expression to force Tableau to sum up the distinct counts as you intend.

Example Calculation

Below is a sample calculation for the Totals column that applies this trick:

IF LAST() = FIRST() THEN
COUNTD([Opportunity ID])
ELSE
COUNTD(
[Opportunity ID]
)
END

Breakdown:

• IF LAST() = FIRST():
  This condition checks if Tableau is processing a subtotal or grand total row. In these cases, the window of data spans only the subtotal cell.
• WINDOW_SUM(...):
  This function aggregates the values over a specified partition (or pane). By wrapping our FIXED LOD expression with SUM(), we ensure that the subtotal reflects the sum of distinct counts computed at a fixed level of detail.
• ELSE COUNTD(...):
  For individual rows, we simply compute the distinct count as usual.

Set the Table Calculation Partitioning:

1. After creating your calculated field, right-click it and choose Edit Table Calculation.
   • Set Compute Using to either “Pane (Across)” or “Pane (Down)” based on your layout.
   • Proper partitioning ensures that WINDOW_SUM correctly aggregates values for the subtotal.
2. Enable Subtotals:
   In your worksheet, go to Analysis > Totals > Show Subtotals.
   This forces Tableau to use your calculated field for subtotal rows.
3. Test and Validate:
   Compare your results. If you expect a total of 96 distinct opportunities but see a different number, check:
   • Your LOD expression’s dimension.
   • Table calculation partitioning.
   • Whether each condition correctly filters the data.