Recipe Tableau and SQL

Why don't my distinct counts add up to the total?

Q: How do I count member-months or daily active users over a month?

Count per period, then add: SUM({ INCLUDE [month] : COUNTD([member_id]) }) gives member-months, which is what capacity and billing usually need. COUNTD([member_id]) alone gives the people active in the whole range.

Q: My distinct count is one too high in every group. Why?

Look for an ELSE inside a conditional count. COUNTD(IF ... THEN [member_id] ELSE 'none' END) counts 'none' as one more member wherever any row fails the test. Leave the ELSE out so those rows return NULL, which COUNTD skips.

A distinct count isn't additive: a member who takes yoga and spin counts once in each class row but once in the total. At this gym the class counts add up to 532 while the gym has 310 members, and monthly active members add up to 1,156. Both numbers are right; they count different things.

Updated October 30, 2026

The data

Harbor Strength gym, class check-ins in 2025

Synthetic datacheckins.csv

A gym with three studios and five class types. 310 members, 2,055 check-ins over 2025. Most members stick to their home studio but mix two or three kinds of class.

`checkin_id`	`checkin_date`	`month`	`member_id`	`studio`	`class_type`
C-00001	2025-01-03	2025-01	M-0020	Midtown	HIIT
C-00002	2025-01-03	2025-01	M-0167	Midtown	Spin
C-00003	2025-01-03	2025-01	M-0246	Eastside	Strength
C-00004	2025-01-04	2025-01	M-0044	Harbor	Spin

Why is the sum bigger than the total?

Because people belong to more than one group. Harbor Strength has 310 members. Break them down by class type and the bars add up to 532, because most members mix two or three kinds of class. Break them down by month and the bars add up to 1,156: a member who came all year sits in twelve bars. Neither number is a bug. The bars count member-group pairs; the total counts people.

Step 1

Count distinct members per group

Each bar is the number of different members who checked in at least once in that group. Every bar is correct on its own.

Tableau

COUNTD([member_id])
// with [{knob.grain}] on Rows

The xmetric engine ran this formula and got the SQL’s Members column, row for row.

SQL

SELECT
  class_type,
  COUNT(DISTINCT member_id) : : INTEGER AS members
FROM input
GROUP BY class_type
ORDER BY members DESC, class_type

Rows

Class type	Members
Strength	142
Yoga	132
Spin	120
HIIT	101
Mobility	37

Step 2

Add the bars, then count the whole gym

The sum is always at least the distinct count, and equal only when nobody appears in two groups. The INCLUDE expression counts per group first and adds the results, so it gives the sum on purpose; plain COUNTD over the whole table gives the people.

Tableau

SUM({ INCLUDE [class_type] : COUNTD([member_id]) })
// vs COUNTD([member_id])

SQL

WITH p AS (
  SELECT class_type, COUNT(DISTINCT member_id) AS members
  FROM input
  GROUP BY class_type
)
SELECT
  'Sum of the bars' AS total,
  SUM(members) : : INTEGER AS members
FROM p
UNION ALL 
SELECT 'Distinct members', COUNT(DISTINCT member_id) : : INTEGER
FROM input

Rows

Total	Members
Sum of the bars	532
Distinct members	310

Break members down by

How much does the grain change it?

Switch the breakdown. By {{grain}}, the bars add up to 532 against 310 real members, 71.6% too many. Studios overlap least, since 241 members never leave their home studio. Months overlap most, because the same people come back month after month. The finer or more repeatable the group, the further the sum drifts from the people.

Step 3

See how many groups each member is in

This is where the extra comes from. A member in three groups is counted three times by the bars and once by the total. Add up times_counted and you get exactly the sum of the bars.

Tableau

{ FIXED [member_id] : COUNTD([class_type]) }
// as a dimension on Columns, with COUNTD([member_id]) on Rows

SQL

WITH m AS (
  SELECT
    member_id,
    COUNT(DISTINCT class_type) AS groups_per_member
  FROM input
  GROUP BY member_id
)
SELECT
  groups_per_member : : INTEGER AS groups_per_member,
  COUNT(*) : : INTEGER AS members,
  (groups_per_member * COUNT(*)) : : INTEGER AS times_counted
FROM m
GROUP BY groups_per_member
ORDER BY groups_per_member

Rows

Groups per member	Members	Times counted
1	137	137
2	124	248
3	49	147

Step 4

Split each group into its own members and shared ones

The Only here part of each bar is members who appear in no other group. Only those add up cleanly; the shared part is counted again in some other bar. FIXED works here because it ignores the groups on the view and looks at each member across the whole table.

Tableau

IF { FIXED [member_id] : COUNTD([class_type]) } = 1 THEN "Only here"
ELSE "Also elsewhere"
END
// on Color, with COUNTD([member_id])

SQL

WITH m AS (
  SELECT
    member_id,
    COUNT(DISTINCT class_type) AS groups_per_member
  FROM input
  GROUP BY member_id
), j AS (
  SELECT DISTINCT i.class_type, i.member_id, m.groups_per_member
  FROM input i JOIN m USING (member_id)
)
SELECT
  class_type,
  CASE
    WHEN groups_per_member = 1 THEN 'Only here'
    ELSE 'Also elsewhere'
    END AS reach,
  COUNT(*) : : INTEGER AS members
FROM j
GROUP BY ALL
ORDER BY class_type, reach DESC

Rows

Class type	Reach	Members
HIIT	Only here	28
HIIT	Also elsewhere	73
Mobility	Only here	9
Mobility	Also elsewhere	28
Spin	Only here	26
Spin	Also elsewhere	94
Strength	Only here	36
Strength	Also elsewhere	106
Yoga	Only here	38
Yoga	Also elsewhere	94

Common mistake

Adding up the rows to get the total

Add up the monthly active members and you get 1,156 "members" for 2025. The gym has 310; loyal members are counted once for every month they came. Count distinct over the whole year, or call the sum what it is: member-months.

Wrong

Point at a bar to read it.

Tableau

COUNTD([member_id])
// month on Rows, Total Using: Sum

SQL

WITH p AS (
  SELECT month, COUNT(DISTINCT member_id) AS members
  FROM input
  GROUP BY month
)
SELECT SUM(members) : : INTEGER AS members_in_2025
FROM p

Right

Point at a bar to read it.

Tableau

COUNTD([member_id])
// month on Rows, Total Using: Automatic

SQL

SELECT COUNT(DISTINCT member_id) : : INTEGER AS members_in_2025
FROM input

Common mistake

An ELSE inside a conditional distinct count

The ELSE value is counted as one more member in every group that has a non-matching row. Here each studio gains one phantom yoga member. Leave the ELSE out: non-matching rows become NULL, and COUNTD skips NULL.

WrongStarts with Eastside, Harbor, Midtown…

Point at a bar to read it.

Tableau

COUNTD(IF [class_type] = 'Yoga' THEN [member_id] ELSE 'none' END)

SQL

SELECT
  studio,
  COUNT(DISTINCT CASE WHEN class_type = 'Yoga' THEN member_id ELSE 'none' END) : : INTEGER AS yoga_members
FROM input
GROUP BY studio
ORDER BY studio

RightStarts with Eastside, Harbor, Midtown…

Point at a bar to read it.

Tableau

COUNTD(IF [class_type] = 'Yoga' THEN [member_id] END)

SQL

SELECT
  studio,
  COUNT(DISTINCT CASE WHEN class_type = 'Yoga' THEN member_id END) : : INTEGER AS yoga_members
FROM input
GROUP BY studio
ORDER BY studio

When is the sum the right number?

When each group owns its count. A class coach plans for the members in their class type, so 532 class-type memberships is the coaches' workload. Member-months (1,156) are what a capacity or billing plan needs. Say which one a number is, right in the label: "members" for people, "member-months" or "class memberships" for the sum.

Run it on your own data

Any table with an id (customer, user, member) and a group column works. Drop your CSV and pick the id and the group; the steps above recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, checkins.csv, shows the shape.

Questions

Which number should I report?

The one that answers the question. "How many people use the gym" is the distinct total. "How many class-type sign-ups do the coaches handle" is the sum of the rows. Label them so nobody adds one to get the other.

Why does Tableau's grand total show a smaller number than the rows add up to?

With Total Using set to Automatic, Tableau recomputes COUNTD over all the rows under the total, so a person counts once. Set Total Using to Sum if you want the rows added, or use SUM({ INCLUDE [category] : COUNTD([member_id]) }).

How do I count member-months or daily active users over a month?

Count per period, then add: SUM({ INCLUDE [month] : COUNTD([member_id]) }) gives member-months, which is what capacity and billing usually need. COUNTD([member_id]) alone gives the people active in the whole range.

My distinct count is one too high in every group. Why?

Look for an ELSE inside a conditional count. COUNTD(IF ... THEN [member_id] ELSE 'none' END) counts 'none' as one more member wherever any row fails the test. Leave the ELSE out so those rows return NULL, which COUNTD skips.

Is an approximate distinct count good enough?

On very large tables, often: SQL engines offer HyperLogLog functions such as approx_count_distinct, usually within a few percent. On a CSV of a few hundred thousand rows the exact count is cheap, so use it.

Sources

Tableau: Level of detail expressions checked October 30, 2026
Tableau: Configure grand totals (Total Using) checked October 30, 2026