xmetricRecipes

Recipe Tableau and SQL

Why does my percent of total change when I filter?

SUM([x]) / TOTAL(SUM([x])) divides by whatever is left after the filter, so the visible rows always add up to 100%. To divide by the whole dataset, use MIN({ FIXED : SUM([x]) }) in Tableau, or compute SUM(x) OVER () in a CTE before the WHERE in SQL. Filter this library system to the East Side and Riverside reads 47% of checkouts, more than the main Central library's real 32%, though Riverside is only 13.5% of the city.

Updated

The data

Ashford Public Library, checkouts by branch, month and format, 2025

Synthetic datacheckouts.csv

A city library system with eight branches in three areas. The big Central library on the West Side does about a third of all checkouts; Riverside leads the East Side.

monthbranchareaformatcheckouts
2025-01-01CentralWest SidePrint6293
2025-01-01CentralWest SideE-book2155
2025-01-01CentralWest SideAudiobook1192
2025-01-01Harbor ViewWest SidePrint1776

Step 1

Share of all checkouts, no filter

With nothing filtered, TOTAL() is the whole city and the shares are honest. Central does about a third of everything; the other seven split the rest.

Tableau

SUM([checkouts]) / TOTAL(SUM([checkouts]))
// Compute Using: branch

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

SQL

WITH b AS (
  SELECT branch, area, SUM(checkouts) AS checkouts
  FROM input
  GROUP BY branch, area
)
SELECT
  branch,
  area,
  checkouts,
  checkouts / SUM(checkouts) OVER () AS share_of_city
FROM b
ORDER BY checkouts DESC

Rows

BranchAreaCheckoutsShare of city
CentralWest Side122,67232.4%
RiversideEast Side50,97813.5%
NorthgateNorth44,57511.8%
Harbor ViewWest Side37,0119.8%
LindenNorth34,4609.1%
Maple HillWest Side31,0298.2%
Elm ParkEast Side29,3017.7%
FoundryEast Side28,2277.5%

Why does a branch jump from 13.5% to 47%?

Because the denominator changed, not the branch. Filter to the East Side and the formula divides Riverside by the East Side's checkouts, not the city's. Turn the filter and watch every bar grow.

Filter to area

Step 2

Filter to one area and watch TOTAL() move

Same formula, new denominator. The filter removes rows first, then TOTAL() adds up what is left, so the visible bars sum to 100% again. Every bar grows, and a mid-sized branch can look like the biggest in the city.

Tableau

SUM([checkouts]) / TOTAL(SUM([checkouts]))
// Filters: area = {knob.area}

SQL

WITH b AS (
  SELECT branch, SUM(checkouts) AS checkouts
  FROM input
  WHERE ('{knob.area}' = 'All areas' OR area = '{knob.area}')
  GROUP BY branch
)
SELECT
  branch,
  checkouts,
  checkouts / SUM(checkouts) OVER () AS share_of_visible
FROM b
ORDER BY checkouts DESC

Rows

BranchCheckoutsShare of visible
Riverside50,97847%
Elm Park29,30127%
Foundry28,22726%

Common mistake

Calling the share of the visible rows 'share of total'

Filtered to the East Side, Riverside reads 47% under a 'share of total' title, more than Central's real 32%. Riverside is 13.5% of the city. If the title says total, the denominator has to be the total.

WrongStarts with Riverside, Elm Park, Foundry…
Point at a bar to read it.
0%25%50%75%100%RiversideElm ParkFoundry

Tableau

SUM([checkouts]) / TOTAL(SUM([checkouts]))
// titled 'Share of all checkouts', area filter on

SQL

WITH b AS (
  SELECT branch, SUM(checkouts) AS checkouts
  FROM input
  WHERE area = 'East Side'
  GROUP BY branch
)
SELECT
  branch,
  checkouts / SUM(checkouts) OVER () AS share_of_total
FROM b
ORDER BY share_of_total DESC
RightStarts with Riverside, Elm Park, Foundry…
Point at a bar to read it.
0%25%50%75%100%RiversideElm ParkFoundry

Tableau

SUM([checkouts]) / MIN({ FIXED : SUM([checkouts]) })

SQL

WITH b AS (
  SELECT branch, area, SUM(checkouts) AS checkouts
  FROM input
  GROUP BY branch, area
), s AS (
  SELECT
    branch,
    area,
    checkouts / SUM(checkouts) OVER () AS share_of_total
  FROM b
)
SELECT branch, share_of_total
FROM s
WHERE area = 'East Side'
ORDER BY share_of_total DESC

How do I keep the denominator fixed?

Compute the total before the filter runs. In Tableau that is a FIXED expression; in SQL, a window in a CTE with the WHERE outside it.

Step 3

Pin the denominator to the whole city

{ FIXED : SUM([checkouts]) } is computed before ordinary filters, so it stays the city total. MIN() turns the repeated per-row value into one number per mark. In SQL the window runs in the CTE over all rows and the WHERE comes after it. Now the bars no longer sum to 100%, and that is the point.

Tableau

SUM([checkouts]) / MIN({ FIXED : SUM([checkouts]) })
// Filters: area = {knob.area}

SQL

WITH b AS (
  SELECT branch, area, SUM(checkouts) AS checkouts
  FROM input
  GROUP BY branch, area
), s AS (
  SELECT
    branch,
    area,
    checkouts,
    checkouts / SUM(checkouts) OVER () AS share_of_city
  FROM b
)
SELECT branch, checkouts, share_of_city
FROM s
WHERE ('{knob.area}' = 'All areas' OR area = '{knob.area}')
ORDER BY checkouts DESC

Rows

BranchCheckoutsShare of city
Riverside50,97813.5%
Elm Park29,3017.7%
Foundry28,2277.5%

Step 4

Show both, side by side

Both numbers are correct; they answer different questions. The gap between the bars is the part of the city the filter hid. Pick All areas and the two bars match.

Tableau

Share of visible: SUM([checkouts]) / TOTAL(SUM([checkouts]))
// Share of city: SUM([checkouts]) / MIN({ FIXED : SUM([checkouts]) })

SQL

WITH b AS (
  SELECT branch, area, SUM(checkouts) AS checkouts
  FROM input
  GROUP BY branch, area
), s AS (
  SELECT
    branch,
    area,
    checkouts / SUM(checkouts) OVER () AS share_of_city
  FROM b
), f AS (
  SELECT
    branch,
    share_of_city,
    share_of_city / SUM(share_of_city) OVER () AS share_of_visible
  FROM s
  WHERE ('{knob.area}' = 'All areas' OR area = '{knob.area}')
)
SELECT
  branch,
  'Share of visible branches' AS denominator,
  share_of_visible AS share
FROM f
UNION ALL 
SELECT branch, 'Share of all branches', share_of_city
FROM f
ORDER BY branch, denominator

Rows

BranchDenominatorShare
Elm ParkShare of all branches7.7%
Elm ParkShare of visible branches27%
FoundryShare of all branches7.5%
FoundryShare of visible branches26%
RiversideShare of all branches13.5%
RiversideShare of visible branches47%

Why is my share tiny after switching to FIXED?

The FIXED total is on every row, and SUM() adds it once per row. Wrap it in MIN().

Common mistake

Summing the FIXED denominator

A FIXED value repeats on every row. Wrapped in SUM(), it is added once per row under each mark: Riverside has 36 rows (12 months, 3 formats), so its share shrinks 36 times, to 0.4%. Use MIN(), MAX() or ATTR() around the FIXED part.

WrongStarts with Central, Riverside, Northgate…
Point at the line to read it.
0%25%50%75%100%Branches in this order →

Tableau

SUM([checkouts]) / SUM({ FIXED : SUM([checkouts]) })

SQL

WITH r AS (
  SELECT *, SUM(checkouts) OVER () AS city_total
  FROM input
)
SELECT branch, SUM(checkouts) / SUM(city_total) AS share
FROM r
GROUP BY branch
ORDER BY share DESC
RightStarts with Central, Riverside, Northgate…
Point at the line to read it.
0%25%50%75%100%Branches in this order →

Tableau

SUM([checkouts]) / MIN({ FIXED : SUM([checkouts]) })

SQL

WITH r AS (
  SELECT *, SUM(checkouts) OVER () AS city_total
  FROM input
)
SELECT branch, SUM(checkouts) / MIN(city_total) AS share
FROM r
GROUP BY branch
ORDER BY share DESC

Run it on your own data

Any table with a category, a group you might filter by and a measure works. Drop your CSV and pick the columns; 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, checkouts.csv, shows the shape.

Questions

Which denominator is right?

It depends on the question. 'Share of what I'm looking at' is TOTAL(). 'Share of the whole company' is { FIXED : SUM() }. 'Share of its own region' is { FIXED [region] : SUM() }. Label the column with the denominator, e.g. 'Share of all branches', so nobody has to guess.

My FIXED share still changes when I filter. Why?

The filter was added to context (it shows grey on the Filters shelf). Context filters run before FIXED expressions; ordinary dimension filters run after. Remove it from context, or keep it there on purpose if the denominator should follow it.

Can I hide rows but keep TOTAL() over everything?

Yes: filter on a table calculation instead of the dimension. Put LOOKUP(MIN([branch]), 0) on Filters; table-calculation filters run last, after TOTAL() has seen every branch.

How is this done in SQL?

Window functions run after WHERE. Compute the share inside a CTE over all rows, then filter in the outer query. Filtering first and windowing second gives the share of the visible rows.

Sources