DEV Community: Michael Nocito

One Cell, Many Values: When a Column Holds a List

Michael Nocito — Tue, 08 Sep 2026 13:00:05 +0000

By Michael Nocito, data analyst · Updated August 19, 2026

You ask a table for its list of categories, and the answer is 2,923. You were expecting about thirty. Nothing errored, nothing warned you, and the number is not a bug. It is the correct answer to a question you did not mean to ask. This happens when a column stores a list inside a single cell, and it quietly breaks DISTINCT, COUNT, and GROUP BY all at once. Here is how to spot it in under a minute, why the number came back wrong, and three ways to work with the column, easiest first.

The one-sentence version. If a cell holds Action,Indie,RPG instead of one value, then DISTINCT counts unique combinations , not unique values, and every count built on top of it inherits the error.

The worked example throughout is a public Steam games dataset of 125,855 rows with a Genres column. Every number below is a real result from it, including the wrong one.

The symptom: a distinct count that is far too big

The opening move on any new column is to ask what values it holds:

SELECT COUNT(DISTINCT Genres) AS genre_count
FROM games_raw;

The answer came back 2,923. A digital storefront does not have 2,923 genres. It has a menu of a few dozen that a developer picks from.

That gap between the number you got and the number you expected is the single most valuable signal in this whole guide. It is not an inconvenience to route around, it is the finding. Whenever a distinct count on a categorical column comes back in the thousands, stop, because the column is almost certainly not holding what you think it is.

Look at the values before you count them

Before theorising, look. A count tells you how many, never what:

SELECT DISTINCT Genres
FROM games_raw
LIMIT 20;

Genres
Adventure
Casual
Casual,Indie,Simulation
Action,Early Access
Action,Adventure
Simulation,Strategy

There it is. Some cells hold one genre and some hold three, separated by commas. The column is not storing a genre, it is storing a list of genres as a piece of text.

The habit worth stealing. Look first, measure second. If you measure first, you can only ever confirm or deny the thing you already suspected, and the thing you did not suspect stays invisible. Twenty rows of eyeballing is the cheapest step in analysis and it catches problems no summary statistic will report. The Exploratory Data Analysis guide builds the rest of that opening routine.

Measure how widespread it is

Twenty rows is an impression. Turn it into evidence, because the answer decides how much work you are in for. If a handful of rows hold lists, that is a small cleanup. If nearly all of them do, the shape of the column is the problem:

SELECT COUNT(DISTINCT Genres) AS values_with_a_comma
FROM games_raw
WHERE Genres LIKE '%,%';

2,898 of the 2,923 distinct values contain a comma. That is 99 percent. This is not an edge case in the data, it is the data.

(LIKE '%,%' reads as "anything, then a comma, then anything." If that syntax is new, LIKE and Wildcards covers it.)

Why DISTINCT gave you the wrong answer

DISTINCT did exactly what it promises. It returned each different value in the column once. The problem is that a "value" here is a whole comma separated string, and it has no idea the commas mean anything:

Cell contents	What DISTINCT sees
`Indie`	One value
`Casual,Indie`	A different value
`Casual,Indie,Simulation`	A third, different value

Three rows in your result, and the word Indie is in all three. So 2,923 is not the number of genres. It is the number of unique combinations of genres that at least one game uses. That is a real fact about the data, it just is not the fact anybody asked for.

The same distortion runs through every aggregate you build on the column. GROUP BY Genres makes 2,923 groups, most with a handful of games in them. A bar chart of it is unreadable. A percentage share computed from it is wrong, because a game tagged with three genres is counted once, in a bucket of its own, rather than once per genre.

The values that never appear alone

Here is the trap that catches people who think they have solved it. Flip the comma filter around and you get the cells holding exactly one genre, which are genre names standing on their own:

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres NOT LIKE '%,%'
ORDER BY Genres;

That returns 24 rows, and they look right. Action, Adventure, Casual, Indie, RPG, Racing, Simulation, Sports, Strategy, and so on. It is tempting to call that the genre list and move on.

It is incomplete, and nothing in the result says so. A value that is only ever applied alongside another one will sit inside the 2,898 lists and never once appear on its own. Probe for one:

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres LIKE '%Gore%';

132 combinations come back, and not one of them is Gore by itself. Gore is real, it is used, and the 24 row list missed it completely. Splitting the column properly turns up 33 distinct values, so nine of them never stand alone.

The general rule. Filtering a list column to the rows with one item gives you the values that can appear alone, not the values that exist. Those are different sets, and the difference is silent. Treat that 24 as a first draft to check, never as the answer.

Three ways to work with it, easiest first

1. Do not split it, filter it with LIKE

If your real question is "show me the RPGs," you never needed the full genre list. Ask whether the cell contains the tag:

SELECT Name, Price
FROM games_raw
WHERE Genres LIKE '%RPG%';

This works whether the tag sits first, last, or in the middle. It needs no new syntax beyond the wildcards, and for most day to day filtering it is the right answer. Guard against matching part of a longer word by wrapping the value in commas first:

SELECT Name, Price
FROM games_raw
WHERE ',' || Genres || ',' LIKE '%,RPG,%';

Good for: filtering to one or two known categories. Not good for: counting games per genre across all of them, which would mean writing one query per genre.

2. Match against a list you already have

If the categories come from a fixed menu, you often have that menu somewhere, or can write it down once. Put it in a small table and join with a pattern:

SELECT g.genre, COUNT(*) AS games
FROM genre_list AS g
JOIN games_raw AS r
  ON ',' || r.Genres || ',' LIKE '%,' || g.genre || ',%'
GROUP BY g.genre
ORDER BY games DESC;

This gives a proper count per genre without any string surgery, and it has a quiet advantage: because the list is yours, a genre with zero games still shows up when you switch to a LEFT JOIN, which a split can never tell you.

Good for: a known, stable set of categories. Not good for: discovering categories you did not know were in there, which is the exact problem the 24 row list had.

3. Split the list into one row per value

The complete answer. Break each list apart so one game with three genres becomes three rows, then group normally. Standard SQL has no split function, so this walks the text one comma at a time:

WITH RECURSIVE split(genre, rest) AS (
  SELECT '', Genres || ','
  FROM games_raw
  WHERE Genres IS NOT NULL AND Genres <> ''
  UNION ALL
  SELECT substr(rest, 1, instr(rest, ',') - 1),
         substr(rest, instr(rest, ',') + 1)
  FROM split
  WHERE rest <> ''
)
SELECT trim(genre) AS genre,
       COUNT(*) AS games
FROM split
WHERE trim(genre) <> ''
GROUP BY lower(trim(genre))
ORDER BY games DESC;

Reading it in words: start each game off with an empty genre and its full list with a comma glued on the end, so the last item has a delimiter too. Then repeatedly cut at the first comma, keeping the piece before it and carrying the rest forward, until nothing is left. instr finds the position of the comma and substr cuts the text. SQL CTEs explains the WITH block itself.

That returns 33 genres with a game count each, and it is the version you would put in a report.

Good for: counts per category, charts, anything needing the complete set. Not good for: a first look, since it is the most syntax for the least immediate payoff.

Most databases have a shortcut. The recursive version above works everywhere, which is why it is worth seeing once, but you rarely need it: PostgreSQL has unnest(string_to_array(genres, ',')), SQL Server has STRING_SPLIT(genres, ','), MySQL 8 has JSON_TABLE, BigQuery has UNNEST(SPLIT(genres, ',')), and DuckDB has unnest(str_split(genres, ',')). Check yours before writing the long form.

Which one to pick

Your question	Use
Show me the rows in one category	LIKE filter
How many rows in each of a few known categories	LIKE filter, or the list join
How many rows in every category	Split, or the list join
What categories even exist in here	Split, it is the only one that discovers
A chart of category share	Split, and say in the caption that rows count once per category

That last row matters and gets skipped. Once you split, a game tagged with three genres appears in three rows, so the genre counts add up to more than the number of games. Nothing is wrong, but a reader will assume the parts sum to the whole unless you tell them otherwise.

Write it down as a limitation

A multi value column is exactly the kind of thing that belongs in your write up rather than buried in a query. It changes what your numbers mean. Three lines cover it:

What the column actually holds. "The Genres field stores a comma separated list, so a game can carry several genres."
How you handled it. "Genres were split to one row per game per genre before counting."
What that does to the totals. "Games with several genres are counted once in each, so genre counts sum to more than the 125,855 games."

There is a second thing worth flagging in this particular dataset, and it is the kind of detail that separates a real analysis from a tutorial. Those 33 values are not all genres. Gore, Violent, Nudity and Sexual Content are content warnings. Accounting, Photo Editing and Video Production are software categories, not games at all. The field mixes several kinds of label into one column, so "top genre" is a misleading headline unless you decide which of the 33 count and say which you dropped. Documenting Data Limitations covers writing that section without undermining your own work.

Why the data looks like this

It helps to know this is a known shape with a known name, not a mess somebody made by accident. A database is described as being in first normal form when every cell holds a single value. A column packing a list into one cell breaks that rule, and the textbook fix is a separate small table with one row per pairing:

game_id	genre
101	Action
101	Indie
101	RPG
102	Casual

With that table, counting per genre is an ordinary GROUP BY and none of this guide is needed. So why does the flat version keep turning up? Because analysts receive data rather than design it. Exports flatten. Spreadsheets get typed by hand. APIs return an array and whoever wrote the CSV joined it with commas. Survey tools write "select all that apply" answers into one field as a matter of course.

You will meet this column shape in tags, skills, categories, region lists, product options, and multi select survey questions. Once you have recognised it once, you recognise it everywhere, and the first thing you will do on any new categorical column is look at twenty rows before you count them.

More detail on this, and more like it. Every how-to sits in one place on the guides index: SQL, Tableau, data migration, and the working habits around them.

Originally published on Analyst Prep Kit: One Cell, Many Values: When a Column Holds a List

Visit the site for more beginner data analysis guides and free resources: the full guide archive covers SQL, Excel, Power BI, Tableau, Python and statistics, and the practice kits run in your browser with nothing to install.

If it was useful: Buy Me a Coffee.

LIKE in SQL, Explained for Beginners

Michael Nocito — Mon, 07 Sep 2026 13:00:28 +0000

By Michael Nocito, data analyst · Updated August 19, 2026

Most of the time you ask SQL for an exact value. WHERE status = 'Active' is a closed question with a yes or no answer. But a lot of real columns do not hold one tidy value. They hold a sentence, a product name with a code stuck on the front, an email address, or a list of tags jammed into a single cell. For those you need a looser question, and LIKE is how you ask it. It is a small piece of syntax with exactly two moving parts, and once those click you can search inside text instead of only matching it.

The one-sentence version. = asks "is this value exactly this?" LIKE asks "does this value match this shape?" The shape is written with two wildcards: % stands for any run of characters including none, and _ stands for exactly one character.

Why equals asks a stricter question than you want

Take a products table with a name column:

id	name
1	Blue Cotton Shirt
2	Shirt Stay Clips
3	Red Wool Sweater
4	Shirt

You want the shirts. The obvious thing fails:

SELECT name
FROM products
WHERE name = 'Shirt';

That returns one row, id 4, because = compares the whole value start to finish. Blue Cotton Shirt is not the same string as Shirt, so it is excluded, and correctly so. Equals is doing its job. It is just the wrong question.

The question you actually have is "does the name contain the word Shirt anywhere in it?" That is a pattern, and patterns are what LIKE is for:

SELECT name
FROM products
WHERE name LIKE '%Shirt%';

Three rows come back: Blue Cotton Shirt, Shirt Stay Clips, and Shirt.

The two wildcards, % and _

Everything LIKE can do comes from two characters.

Wildcard	Means	Example	Matches
`%`	Any run of characters, including none at all	`'S%'`	`S`, `Sam`, `Sweater`
`_`	Exactly one character, no more, no fewer	`'S_m'`	`Sam`, `Sum`, but not `Steam`

The "including none at all" part of % is the bit people miss. 'S%' matches the single letter S, because % is allowed to stand for nothing. The underscore is not so forgiving. It demands a character be there.

Say this one out loud before you read the answer: what would '_at' match, and would flat be in the list? Commit to an answer first.

It matches three-character values ending in at, so cat, hat, bat. flat is out, because _ covers one character and flat has two before the at. If you wanted both you would write '%at'.

You can mix them. 'A__-%' matches a value that starts with A, has exactly two more characters, then a hyphen, then anything. That is a useful shape for product codes and account numbers, where the format is fixed but the content is not.

Where you put the % is the whole question

Same wildcard, four positions, four completely different questions. This is the table worth memorising:

You write	You are asking	Matches "Blue Cotton Shirt"?
`LIKE 'Shirt'`	Is it exactly Shirt (same as `=`)	No
`LIKE 'Blue%'`	Does it start with Blue	Yes
`LIKE '%Shirt'`	Does it end with Shirt	Yes
`LIKE '%Cotton%'`	Does it contain Cotton anywhere	Yes

A LIKE pattern with no wildcard in it is just a slower =. If you write LIKE 'Shirt' you have asked for an exact match and should use = instead, which is clearer to whoever reads the query next.

Read the pattern left to right, in words. '%Cotton%' reads as "anything, then Cotton, then anything." 'Blue%' reads as "Blue, then anything." If you can say the pattern as a sentence, you will not put the percent signs in the wrong place.

NOT LIKE, and finding what is missing

Put NOT in front and you get the opposite set:

SELECT name
FROM products
WHERE name NOT LIKE '%Shirt%';

That returns the rows with no Shirt in them. This is more useful than it first looks, because "which rows do not fit the expected shape" is one of the most productive questions in data cleaning. Emails with no at sign, product codes missing their prefix, a text column where some rows hold a list and some do not. NOT LIKE finds the odd ones out.

One catch worth knowing now. A row where the column is NULL is returned by neither LIKE nor NOT LIKE. NULL means unknown, and SQL will not claim an unknown value matches or fails to match a pattern. If you want the blanks too, ask for them: WHERE name NOT LIKE '%Shirt%' OR name IS NULL. The full story is in NULL in SQL.

Case sensitivity, which changes by database

Whether '%shirt%' finds Blue Cotton Shirt depends on which database you are sitting in. This surprises people who move between two of them, so it is worth checking once rather than assuming.

Database	Is LIKE case sensitive?	How to force insensitive
SQLite	No for plain A-Z letters, yes for accented and non-English ones	Already insensitive for A-Z
PostgreSQL	Yes	Use `ILIKE`
MySQL	Depends on the column collation, usually no	Usually already insensitive
SQL Server	Depends on the collation, usually no	Usually already insensitive

The portable move, if you want the same answer everywhere, is to flatten both sides yourself:

SELECT name
FROM products
WHERE LOWER(name) LIKE '%shirt%';

That works in every database listed above, at the cost of a little speed. See the last section for why.

The over-matching trap

Predict this one before reading on. You search a genre column with LIKE '%Video%'. What comes back?

In a real Steam games dataset, that pattern matches both Video Production and 360 Video, because both contain those five letters. That may be exactly what you wanted, or it may quietly double your result set.

% does not know about word boundaries. It matches letters, not meaning. Some classics:

Pattern	You meant	You also get
`'%art%'`	art	cart, start, Bart, particle
`'%man%'`	man	manage, human, Germany
`'%IT%'`	the IT department	credit, exit, monitor (where LIKE is case insensitive)

The fix is to give the pattern more to hold on to. If the values are separated by a known character, include it: '%, Sales%' is far more precise than '%Sales%'. The next section shows the reliable version of that trick.

Searching a column that holds a list

This is where analysts reach for LIKE most often. Some columns pack several values into one cell, separated by commas:

name	genres
Game A	Action,Indie,RPG
Game B	Casual,Indie
Game C	RPG

= is useless here, because almost no cell holds only RPG. LIKE handles it without any splitting:

SELECT name
FROM games
WHERE genres LIKE '%RPG%';

That finds Game A and Game C, whether the tag sits first, last, or buried in the middle.

The precise version guards against the over-matching trap. Wrap the value in the separator on both sides, then search for the separated tag:

SELECT name
FROM games
WHERE ',' || genres || ',' LIKE '%,RPG,%';

The || glues text together, so Action,Indie,RPG temporarily becomes ,Action,Indie,RPG,. Now every tag has a comma on both sides, including the first and last, and the pattern '%,RPG,%' can only match a whole tag. A genre called RPGMaker would no longer be a false hit. Use CONCAT(',', genres, ',') instead of || in MySQL and SQL Server.

That column shape causes more trouble than just searching, including a distinct count that comes back wrong and never says so. One Cell, Many Values covers the whole problem.

Matching a literal % or _

Every so often the character you are hunting for is a wildcard. Searching a notes column for an actual percent sign with LIKE '%%%' matches every row, which is not useful. Declare an escape character and mark the literal one:

SELECT note
FROM feedback
WHERE note LIKE '%!%%' ESCAPE '!';

Read that pattern as: anything, then a real percent sign (the one after the !), then anything. The ESCAPE '!' at the end is what tells SQL that ! is the marker. You can pick any character for the job as long as it does not appear in your data. The same applies to a literal underscore, which matters more than you would think, because underscores are everywhere in column names and product codes.

Why LIKE '%text%' gets slow

An index on a text column works like the index at the back of a book: entries sorted alphabetically, so the database can jump straight to the right place. That works when you know how the value starts.

Pattern	Can an index help?	Why
`'Blue%'`	Yes	Everything starting with Blue is filed together
`'%Shirt'`	No	The start is unknown, so there is nowhere to jump to
`'%Cotton%'`	No	Same problem, every row has to be read and checked

On a few thousand rows this is invisible. On tens of millions it is the difference between an instant answer and a coffee break. It is not a reason to avoid '%text%', it is a reason to know why the query got slow when the table grew. If you need fast searching inside text at scale, that is a full text search index, a different tool. Indexing for Analysts covers what an index can and cannot do.

The same logic explains the cost of LOWER(name) LIKE ... from earlier. Wrapping the column in a function means the index on that column no longer applies, because the index stores the original values, not the lowercased ones.

A LIKE cheat sheet

You want…	Write
Contains a word	`LIKE '%word%'`
Starts with	`LIKE 'word%'`
Ends with	`LIKE '%word'`
Exactly this	`= 'word'`, not LIKE
Does not contain	`NOT LIKE '%word%'`
Exactly one unknown character	`LIKE 'A_C'`
A fixed format, like three letters then a dash	`LIKE '___-%'`
Case insensitive everywhere	`LOWER(col) LIKE '%word%'`
Case insensitive in PostgreSQL	`ILIKE '%word%'`
One whole tag from a comma separated list	`','
A literal percent sign	{% raw %}`LIKE '%!%%' ESCAPE '!'`

Say it out loud before you run it. Read your pattern left to right as a sentence, swapping "anything" in for every %. If the sentence you say is not the question you meant to ask, the percent signs are in the wrong place. That one habit prevents most LIKE mistakes.

More detail on this, and more like it. Every how-to sits in one place on the guides index: SQL, Tableau, data migration, and the working habits around them.

Originally published on Analyst Prep Kit: LIKE in SQL, Explained for Beginners

If it was useful: Buy Me a Coffee.

Net Present Value (NPV): How to Discount Cash Flows and Read the Answer

Michael Nocito — Mon, 07 Sep 2026 13:00:20 +0000

By Michael Nocito, data analyst · Published August 11, 2026

By the end of this page you can take a project that costs money now and pays money later, and produce one number that says whether it is worth doing. You will know the discount factor and where it comes from, the SQL that computes NPV in one line, the three ways that line silently returns the wrong answer, and how to present a result whose whole meaning depends on an assumption you chose.

The fastest way in is to do it once on something real. Take any project with an upfront cost and a few years of expected returns. Divide each future year's cash by 1.10 raised to that year's number, add them all up, then subtract the upfront cost. If what is left is positive, the project beats a 10% required return.

The whole idea in one line: a dollar arriving in five years is not a dollar. NPV shrinks every future amount down to what it is worth today, then adds the whole stream up, including the negative one at the start.

The shrinking is the idea the rest of the page rests on, so it gets the picture.

The original carries a diagram here. In words: Five vertical bars rising from a common baseline, one for each of years one to five. The full height of each bar is the cash the project expects to collect in that year, and the bars grow steadily taller from left to right, from one hundred and fifty thousand in year one to one hundred and ninety thousand in year five. Each bar is split into two parts. The solid accent-colored part at the bottom is what that cash is worth today after discounting at ten percent, and the pale hatched cap sitting on top of it is the part the waiting takes away. The solid parts shrink steadily from left to right, from about one hundred and thirty six thousand down to about one hundred and eighteen thousand, while the pale caps grow from a thin sliver in year one to a thick block in year five. A dashed line joins the tops of the five solid parts and slopes downward across the chart, against bars that are getting taller. That opposition is the whole point: a payment can be bigger and still be worth less, because each extra year of waiting divides it by another factor of one point one. A small legend at the top right pairs a pale swatch with the word waiting and a solid swatch with the word today.

Every number on this page is verified, and you can check them. The worked project is six rows, shown in full below. Every discount factor, present value, NPV, rate table and error figure was computed in SQLite and cross-checked in pandas before it went on the page, so you can check any row on a calculator and it will agree.

1. Why a future dollar is worth less, in arithmetic rather than words

You are offered $100 today or $100 in a year. Beyond simple impatience, name one concrete reason today is worth more.

Because $100 today can be doing something for the next year. Put it somewhere that returns 10% and by this time next year it is $110. So $100 today and $100 next year are not the same offer, and the size of the difference is whatever return you could have got.

Run that backwards and you have the whole method. If $100 today grows to $110 in a year, then $110 arriving in a year is worth exactly $100 today. Growing forwards multiplies by 1.10. Coming backwards divides by 1.10. The dividing is called discounting, and the 10% is called the discount rate.

Do it twice for two years. $100 today grows to $110, then to $121. So $121 in two years is worth $100 today, and the division is by 1.10 twice, which is 1.21. Three years is 1.331. The number you divide by grows fast, which is why money far in the future gets cut down hard.

Two numbers to hold on to, both from this page's project. At a 10% discount rate, a dollar arriving in five years is worth 62 cents today. Raise the rate to 15% and the same dollar is worth 50 cents. Nothing about the dollar changed. The rate did.

2. The discount factor, and the year-0 rule

What should the discount factor be for money you spend today?

One. Money spent today is already in today's dollars, so it does not get discounted at all, and dividing by 1 leaves it alone. That is not a special case bolted onto the formula. It falls out of it, because any number raised to the power of 0 is 1.

The discount factor for year t at rate r is (1 + r) raised to the power t. Present value is the cash flow divided by that factor.

Year	Factor at 10%	How it is built	A dollar is worth
0	1.0000	1.10 to the power 0	100 cents
1	1.1000	1.10	91 cents
2	1.2100	1.10 × 1.10	83 cents
3	1.3310	1.10 × 1.10 × 1.10	75 cents
4	1.4641	and again	68 cents
5	1.61051	and again	62 cents

Net present value is the sum of every year's present value, with the upfront cost included as a negative number at year 0. The word "net" is that subtraction. A present value that ignores the cost is not an NPV, and section five shows what that mistake looks like when it reaches a slide.

The decision rule is one line. NPV above zero means the project earns more than the rate you required of it, so it adds value. NPV below zero means it does not. NPV of exactly zero means it earns precisely the required return, no more.

3. The worked project, row by row

A project costs $500,000 today and pays back $850,000 over five years. Guess whether it clears a 10% required return, and by roughly how much.

Here is the project. A production line expansion: $500,000 to build, then five years of extra cash from the capacity it adds. The required return is 10%.

Year	Cash flow	Factor at 10%	Present value
0	-500,000	1.0000	-500,000
1	150,000	1.1000	136,364
2	160,000	1.2100	132,231
3	170,000	1.3310	127,724
4	180,000	1.4641	122,942
5	190,000	1.61051	117,975
NPV	350,000		137,236

Check one row by hand to trust the rest. Year 3: 170,000 divided by 1.331 is 127,723.5, which rounds to 127,724. Add the present-value column and you get 137,236.

Look at the two totals on the bottom row, because they are the whole lesson. Undiscounted, the project nets $350,000. Discounted at 10%, it nets $137,236. The $212,764 difference is not a cost anyone pays and not a number in any ledger. It is the return you would have earned on that money elsewhere over five years, and NPV charges the project for it.

The verdict, in a sentence someone can act on: the line expansion is worth $137,236 more than the 10% return we could get elsewhere on the same $500,000. Notice how much work "at 10%" is doing in that sentence. Section six is about how much.

Say why the discounted total is smaller than the undiscounted one, in your own words, before reading on. If you can say it, the rest of this page is careful arithmetic.

4. The query, and the rate written as 10/100

In SQL, what does 10/100 return when both are whole numbers?

Zero. And that turns the whole NPV calculation into something else without changing a single row of data. Follow it through: 10/100 is 0, so 1 + 10/100 is 1, so POWER(1, year) is 1 for every year, so every cash flow is divided by 1 and no discounting happens at all. The query runs, returns $350,000, and $350,000 is just the raw sum of the cash-flow column.

That is the worst kind of wrong. It is not an error, it is not a null, and $350,000 is a believable answer for this project. The only way to catch it is to know it can happen.

The working query, with the rate written as a decimal.

SELECT ROUND(SUM(cash_flow / POWER(1 + 0.10, year)), 0) AS npv
FROM project_cash_flows
WHERE project = 'Line Expansion';        -- 137236

And the same idea one row at a time, which is what you actually want on screen, because a single NPV number is impossible to check.

SELECT year,
       cash_flow,
       ROUND(POWER(1 + 0.10, year), 4)             AS discount_factor,
       ROUND(cash_flow / POWER(1 + 0.10, year), 0) AS present_value
FROM project_cash_flows
WHERE project = 'Line Expansion'
ORDER BY year;

Three notes on the arithmetic, all worth checking once on your own database.

What you write	Returns	Why
`10/100`	0	Whole divided by whole. The rate vanishes.
`10/100.0`	0.1	One decimal makes the whole expression decimal.
`POWER(1 + 10/100, 5)`	1.0	Every factor is 1. No discounting happened.
`POWER(1 + 0.10, 5)`	1.61051	Correct.

POWER itself is safe, because it returns a decimal even when handed whole numbers, so cash_flow / POWER(...) stays decimal. The danger is entirely in how the rate reaches it. Write rates as 0.10. If the rate has to come from a column, make that column a decimal type when the table is created, and check one value before you trust the output. The same trap in its percentage form is worked in gross vs operating vs net margin.

One portability note. POWER exists in SQLite, PostgreSQL, SQL Server, MySQL, Oracle, Snowflake and BigQuery, though older SQLite builds may not have it compiled in. Where it is missing, EXP(year * LN(1 + 0.10)) is the same calculation and works anywhere that has logs.

5. Two off-by-one errors, and which one flips the decision

Your cash-flow table was built with a row number instead of a year number, so the first inflow is labelled 0 instead of 1. Does NPV come out too high or too low?

Too high, and by a lot. Every inflow is being discounted one year less than it should be, so each one is worth more today than it really is, while the cost at year 0 is untouched. On this project the correct answer is $137,236 and the mislabelled one is $200,960, an overstatement of $63,724, which is 46%.

It matters because it can change the answer. Take the same five inflows against a $660,000 upfront cost instead of $500,000.

Version	NPV at 10%	Decision
Inflows correctly at years 1 to 5	-22,764	Reject
Inflows mislabelled 0 to 4	+40,960	Accept

A row number and a year number look identical in a table. Nothing in the query can tell them apart. The check that catches it takes five seconds: the year-0 row is the money you spend today, and it should be the only negative one and the only one whose present value equals its cash flow. If your year-0 row is an inflow, the labels are shifted.

The second off-by-one is worth knowing because it behaves completely differently. If the whole stream shifts, cost and all, so the cost sits at year 1 and the inflows at 2 to 6, then every present value gets divided by one extra 1.10 and NPV goes from $137,236 to $124,760. That is exactly $137,236 divided by 1.10. Because everything moved together, the sign cannot change, so the accept-or-reject decision is always safe even though the value is 9.1% off. Only the partial shift is dangerous, and the partial shift is the one that happens.

The third mistake in this family has nothing to do with years. The upfront cost simply is not in the table, because it lives in a different system, or because someone filtered on year > 0 to drop what looked like an empty row.

SELECT ROUND(SUM(cash_flow / POWER(1 + 0.10, year)), 0)
FROM project_cash_flows
WHERE project = 'Line Expansion' AND year > 0;      -- 637236

$637,236, against a real NPV of $137,236. Overstated by exactly the $500,000 that was left out, and there is nothing in the number itself to say so. That is a present value, not a net present value, and the missing word is the entire cost of the project.

Picture your own company's project table. Where does the upfront cost live, and is it a row in the same table as the returns? If it lives in a capital budget somewhere else, every NPV anyone runs on that table is a gross present value wearing the wrong name.

6. The rate is the assumption, and IRR is the other way round

Same five cash flows, same $500,000 cost. Guess what rate would make this project not worth doing.

Just under 20%. Here is the same project at seven different required returns, with nothing else changed.

Discount rate	NPV	Decision
0%	350,000	Accept
5%	231,791	Accept
10%	137,236	Accept
15%	60,575	Accept
20%	-2,347	Reject
25%	-54,573	Reject
30%	-98,367	Reject

One project, one set of cash flows, and the verdict flips between 15% and 20%. Every row is arithmetically perfect. The rate is not something the data tells you; it is something you choose and then have to defend. An NPV presented without the rate beside it is not a result, it is half of one.

Where the rate comes from, in practice. It is the return the company requires on money it puts at risk, usually its weighted average cost of capital, sometimes a hurdle rate set by the finance team, sometimes the return available on a comparable investment. Riskier projects get a higher rate, which is how risk enters the arithmetic: a higher rate punishes distant cash flows hardest, so speculative projects with far-off payoffs have to be much bigger to clear.

The rate that makes NPV exactly zero has a name: the internal rate of return, or IRR. On this project it is 19.80%. Read it as the project's own return, the break-even required rate. Above it the project is worth doing; below it, not.

IRR is not a SQL calculation. There is no closed-form solution, so it is found by trying rates until NPV lands on zero, which needs iteration that plain SQL does not do. That is why the practical division of labour is: compute NPV in SQL at the rate your company uses, and hand IRR to a spreadsheet or a Python script. If you want an approximate IRR without leaving SQL, run the NPV query at a handful of rates as above and read off where the sign changes. Between 15% and 20% here, and 19.80% when solved properly.

Two more numbers worth having, both from the same project. Payback period, the point where the undiscounted cash adds back up to the cost, is 3.11 years. Discounted payback, the same question in today's dollars, is 3.84 years. Payback is easy to explain and it ignores everything that happens after it lands, which is why it is a useful second number and a poor first one.

7. The full before and after

Same project, two ways of putting it in front of a decision maker.

Before

"The line expansion costs $500,000 and returns $850,000 over five years, a 70% return. Recommend approval." Nothing there is false. The reader has no way to know that the $850,000 arrives slowly, that the return is 70% spread over five years rather than 70% a year, or that the same project loses money if the company's required return is 20%. And the single number carries no rate, so nobody in the room can disagree with it usefully.

After

Measure	Value	Read
Upfront cost	-500,000	Year 0, spent today
Total inflows	850,000	Undiscounted, years 1 to 5
Discount rate used	10%	The company hurdle rate, stated
NPV at 10%	+137,236	Clears the hurdle
NPV at 15%	+60,575	Still clears it
NPV at 20%	-2,347	Does not
IRR	19.80%	The rate where it breaks even
Discounted payback	3.84 years	When today's dollars come back

Same project, four extra rows, and the whole conversation changes. The rate is on the page, so it can be argued with. Two other rates are on the page, so the reader can see how much room there is before the answer changes. And the recommendation now has a shape: fund it if our required return is below about 19.8%, and the margin gets thin above 15%. That is a sentence a finance director can either agree with or correct, which is the most useful thing an analysis can be.

8. Edge cases NPV cannot see

NPV comes out at exactly zero. Accept or reject?

Zero means it earns precisely the required return. It is not a failure and it is not a win. The project would return exactly what the money would have earned elsewhere, so on the numbers alone it is a coin toss, and the decision moves to everything the numbers left out: strategy, risk, what else the money could do, what the team learns by doing it.

Cash flow is not profit. NPV wants cash moving in and out, not accounting profit. Depreciation is a real expense on the income statement and no cash leaves the building, so it does not belong in a cash-flow row. If someone hands you a project's projected profit and asks for an NPV, the first job is converting it back to cash.

The end of the model is not the end of the project. A production line that still works in year 6 has value that a five-year model gives away for nothing. Either extend the model or add a terminal value in the final year. Whichever you do, say so, because a five-year NPV on a fifteen-year asset understates it badly.

The cash flows are forecasts, and NPV reports to the nearest dollar. $137,236 has six digits of apparent precision built on estimates that could be 20% out either way. Presenting NPV to the dollar invites a confidence nobody has earned. Round to the nearest thousand in the summary, keep the full number in the working, and show at least one alternative rate so the range is visible.

Years are not always the right period. If cash arrives monthly, discount monthly, and the rate has to be converted first. A 10% annual rate is not 10 divided by 12 per month; it is 1.10 raised to the power of one twelfth, minus 1, which is about 0.797% a month. Mixing an annual rate with monthly periods is a large error dressed as a small one.

Sign conventions have to be consistent. Every model needs one rule: costs negative, inflows positive. A cost stored as a positive number in a column the query then adds up will not fail, it will just tell you the project is wonderful.

Why this works

Discounting is not a modern convenience. Fisher set out the theory that the value of an asset is its future income discounted to the present, and that the rate connecting them is the market rate of interest (Fisher, 1930, The Theory of Interest , Macmillan). Hirshleifer's later paper is the cleaner statement of why NPV rather than IRR should decide, showing that the present-value rule follows directly from optimal investment choice while rate-of-return rules can rank projects wrongly (Hirshleifer, 1958, Journal of Political Economy , 66(4), 329–352). That is the formal version of section six's point: IRR is a useful second number and a poor rule.

The stronger practical reason to keep the year-by-year table on screen, rather than a single NPV, is that people are bad at future money in a specific and measurable way. Thaler found the implied discount rate people apply falls sharply as the delay lengthens, so a single constant rate does not describe how anyone actually feels about it (Thaler, 1981, Economics Letters , 8(3), 201–207). Frederick, Loewenstein and O'Donoghue's review of the field found implied annual rates in the published literature ranging from below zero to many thousand percent, depending only on how the question was asked (Frederick, Loewenstein, & O'Donoghue, 2002, Journal of Economic Literature , 40(2), 351–401). A reader shown one NPV has to trust your rate. A reader shown the schedule and three rates can find their own.

The question at the top of each section is deliberate. Attempting an answer before receiving one improves learning of that specific material across sixty-four studies (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Guessing what 10/100 returns, before section four told you, is why that one will still be with you the next time you write a rate into a query.

Using this on your own numbers

Rebuilding every capital model in the company is not a job anyone will thank you for. Do this instead, in order.

Show the schedule, not just the NPV. Year, cash flow, factor, present value. Four columns, and it makes every other check on this list possible.
Verify the year-0 row. It should be negative, and its present value should equal its cash flow exactly. If it does not, your factors or your labels are wrong.
Check the rate reached the query as a decimal. Run SELECT POWER(1 + your_rate, 5) on its own. If it returns 1, the rate vanished, and section four has the fix.
Put the rate in the output. A column or a header, not a comment in the code. An NPV without its rate cannot be checked by the person reading it.
Run it at two more rates, one above and one below. This is one more query and it converts a number into a range, which is what the estimate always was.
Find where the sign changes and report that as the approximate IRR. If the decision is close, take the exact figure from a spreadsheet, and say which method you used.

Optional, and only if there is paper to hand. One drawing fixes this. Draw the figure from the top of this page from memory: five outlined bars getting taller left to right, five solid bars in front of them getting shorter, and the point where the two trends cross. Label one axis with the years. If your solid bars grow along with the outlines, the discounting has not landed yet, and the page is still open.

This page is one of five. The Financial analysis set on the guides index is this page plus Gross vs Operating vs Net Margin, Current Ratio vs Quick Ratio, Contribution Margin and Break-Even and Budget vs Actual Variance. They share a worked company, so the numbers carry across. The index also holds every other how-to: SQL, Excel, Tableau, and the working habits around them.

The whole thing on one screen

Cover the right column first. This one works as a test and does very little as a reread.

Concept	What it is
Time value of money	Today's dollar can be invested, so it is worth more than tomorrow's.
Discounting	Dividing a future amount back to what it is worth today.
Discount factor	(1 + rate) raised to the power of the year. At 10%, year 5 is 1.6105.
Present value	Cash flow ÷ discount factor. 190,000 in year 5 is 117,975 today.
The year-0 rule	Factor is 1, so today's money is undiscounted. Anything to the power 0 is 1.
Net present value	Every year's present value added up, including the negative cost.
The word "net"	The upfront cost is in there. Without it you have a present value, not an NPV.
The decision rule	Above zero, it beats the required return. Below zero, it does not.
The worked answer	-500,000 then 150k to 190k over five years is +137,236 at 10%.
The rate trap	Writing 10/100 gives 0, every factor becomes 1, and NPV returns the raw sum.
The partial shift	Inflows labelled 0 to 4 gave 200,960 instead of 137,236, and flipped a reject.
The whole shift	Everything one year late divides NPV by 1.10. Wrong value, safe decision.
The missing cost	Filtering out year 0 gave 637,236. Overstated by exactly the cost.
Rate sensitivity	Same project: +137,236 at 10%, +60,575 at 15%, -2,347 at 20%.
IRR	The rate where NPV is zero. 19.80% here. Needs iteration, so not SQL.
Payback	3.11 years undiscounted, 3.84 discounted. Ignores everything after.
Cash, not profit	Depreciation is an expense and not a cash flow. Convert before discounting.
The deliverable	The schedule, the rate, and NPV at two other rates beside it.

The one habit to keep. If only one thing survives the week, make it this. Never present an NPV without the discount rate next to it, in the output rather than in the code. The rate is the only part of the calculation that was a judgement, it is the part most likely to be wrong, and it is the part the person reading is best placed to correct. If a model fights back in a way this page does not cover, there is a general diagnosis loop for being stuck.

One thing I have never settled, and I would like to know how other people handle it. The first discounted model I built used a rate somebody had typed into a spreadsheet four years earlier, and nobody in the company could say where it came from. What is the oldest unexplained assumption still running in a model you rely on?

References

Fisher, I. (1930). The Theory of Interest: As Determined by Impatience to Spend Income and Opportunity to Invest It. Macmillan.
Hirshleifer, J. (1958). On the theory of optimal investment decision. Journal of Political Economy , 66(4), 329–352.
Thaler, R. (1981). Some empirical evidence on dynamic inconsistency. Economics Letters , 8(3), 201–207.
Frederick, S., Loewenstein, G., & O'Donoghue, T. (2002). Time discounting and time preference: A critical review. Journal of Economic Literature , 40(2), 351–401.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.

Originally published on Analyst Prep Kit: Net Present Value (NPV): How to Discount Cash Flows and Read the Answer

If it was useful: Buy Me a Coffee.

Current Ratio vs Quick Ratio, and Debt to Equity

Michael Nocito — Mon, 07 Sep 2026 13:00:13 +0000

By Michael Nocito, data analyst · Published August 11, 2026

By the end of this page you can look at a balance sheet and answer two questions a lender asks first: can this company pay the bills that are about to come due, and how much of it was bought with borrowed money. You will know the three ratios, the SQL for all of them, and the four places where the definition, not the arithmetic, decides the answer.

One thing to do today, and it takes five minutes. Find any balance sheet, yours or a public company's. Add up cash and receivables, then compare that to the bills due within the year. If the first number is smaller, the company is relying on selling inventory to make its payments, and that is a different business than the totals suggest.

Both questions in one line: liquidity ratios compare what you can turn into cash soon against what you owe soon. Leverage ratios compare what is borrowed against what is owned.

The gap between two companies that look equally healthy is the thing worth seeing first.

The original carries a diagram here. In words: Two panels side by side, each showing a single tall stacked column that represents one company's current assets, measured against a dashed horizontal line that marks the bills coming due within the year. In the left panel, labeled Northlight, the column is built from a solid accent-colored lower block labeled cash and receivables, with a hatched grey block labeled inventory stacked on top of it. The solid block on its own already rises above the dashed line, so this company could pay its bills without selling any inventory, and the panel carries a check mark. In the right panel, labeled Ridgeway, the whole column is taller than Northlight's, which would make its current ratio look better. But the split is different: the solid cash-and-receivables block is short and stops well below the dashed line, and the hatched inventory block above it makes up three quarters of the height. A small bracket marks the gap between the top of the solid block and the dashed line, and this panel carries a cross. The two dashed lines sit at different heights because the two companies owe different amounts. The picture shows that total height, which is what the current ratio measures, says nothing about whether the part you can actually spend reaches the line.

Every number on this page is verified, and you can check them. Two balance sheets are shown in full below, line by line. Every ratio, gap and quarterly figure was computed in SQLite and cross-checked in pandas before it went on the page, so you can add up any column by hand and it will agree.

1. Two questions a balance sheet answers

A company made $560,000 of profit last year. Name something that could still put it out of business in March.

Running out of cash. Profit and cash are not the same thing, and a company can be profitable on paper while having nothing in the bank on the day a supplier wants paying. The income statement tells you whether the business earns; the balance sheet tells you whether it can pay.

A balance sheet is a snapshot on one specific date. It lists what the company owns, called assets, what it owes, called liabilities, and the difference between them, called equity. Two questions are asked of it more often than all the others put together.

Liquidity: can we cover what is due soon with what we can turn into cash soon? That is a question about the near term, usually the next twelve months, and it is answered by the current ratio and the quick ratio.

Leverage: how much of this company was paid for with borrowed money? That is a question about risk over the long term, and it is answered by debt to equity. Borrowed money has to be repaid on a schedule whether or not the business has a good year, which is what makes leverage the thing that turns a bad quarter into a crisis.

Both questions use the same trick: divide one part of the balance sheet by another so the answer does not depend on how big the company is.

2. The current ratio, and what "current" means

What do you think makes an asset "current"?

Time. "Current" means within one year, and it is the only thing the word means here. A current asset is cash, or something expected to become cash within a year: money customers owe you, inventory you expect to sell, expenses you have prepaid. A current liability is a bill due within a year: money you owe suppliers, wages accrued but not yet paid, the slice of a loan repayable this year.

The current ratio is current assets divided by current liabilities. Above 1.0 means there is more coming in soon than going out soon. Below 1.0 means the company needs to raise money from somewhere else to get through the year.

Here is the first of our two balance sheets, at 31 December 2024.

Northlight	Amount
Cash	260,000
Receivables	470,000
Inventory	490,000
Prepaid expenses	40,000
Current assets	1,260,000
Accounts payable	430,000
Accrued expenses	145,000
Short-term debt	125,000
Current liabilities	700,000
Long-term debt	1,065,000
Equity	1,700,000

Current ratio: 1,260,000 divided by 700,000 is 1.80. For every dollar due within the year, there is a dollar eighty of assets expected to arrive within the year. That reads as comfortable, and for this company it is.

3. The quick ratio, and why it drops inventory

Of cash, receivables and inventory, which one might take six months to turn into money, and might never turn into all of it?

Inventory. It is a current asset because you expect to sell it inside a year, and "expect" is doing a lot of work in that sentence. Stock can sit. It can go out of fashion, expire, or need discounting to move. Receivables can be chased and cash is already cash, but inventory is a plan, not a payment.

So the quick ratio runs the same test with inventory taken out. It asks whether the company could meet its near-term bills if nothing on the shelf sold at all. Above 1.0 means yes.

Northlight's quick ratio: 1,260,000 minus 490,000 of inventory is 770,000, divided by 700,000, which is 1.10. It clears the bar without selling a thing.

Now the second company, and this is where the two ratios come apart.

Ridgeway	Amount
Cash	90,000
Receivables	190,000
Inventory	900,000
Prepaid expenses	20,000
Current assets	1,200,000
Accounts payable	310,000
Accrued expenses	105,000
Short-term debt	85,000
Current liabilities	500,000
Long-term debt	520,000
Equity	1,250,000

Ridgeway's current ratio is 1,200,000 divided by 500,000, which is 2.40. That is a third better than Northlight's 1.80, and on a page that only carried the current ratio, Ridgeway is the healthier company.

Its quick ratio is 1,200,000 minus 900,000, which is 300,000, divided by 500,000, which is 0.60. Against $500,000 of bills due inside the year, Ridgeway has $300,000 it can actually spend. It is $200,000 short, and the only way to close that gap is to sell inventory faster than it has been selling it.

The two companies side by side.

Ratio	Northlight	Ridgeway	What it says
Current ratio	1.80	2.40	Ridgeway looks stronger
Quick ratio	1.10	0.60	Ridgeway cannot pay without selling stock
Inventory as a share of current assets	39%	75%	The whole reason for the reversal

The claim this page earns, in one sentence: Ridgeway's current ratio is 33% better than Northlight's and it is $200,000 short of paying next year's bills. That is why the quick ratio exists, and why nobody who lends money reads the current ratio on its own.

Say why a bigger pile of current assets can be the weaker position, in your own words, before reading on.

One more thing about the quick ratio, because it is the first place a definition changes the answer. Some texts define it as current assets minus inventory. Others define it as cash plus marketable securities plus receivables, added up from the top instead of subtracted from the total. Those are not the same. On Northlight's balance sheet the first gives 770,000 and a quick ratio of 1.10; the second gives 730,000 and a quick ratio of 1.04, because prepaid expenses are in current assets and are not something you can pay a supplier with. Same company, same day, two defensible answers 0.06 apart. Write down which one you used.

4. Debt to equity, and the word that has two meanings

A company owes $1,765,000 in total, of which $575,000 is unpaid supplier invoices. Is that $575,000 "debt"?

It depends who is asking, and this is the single most common reason two people compute different debt-to-equity ratios from the same balance sheet.

Debt to equity divides what the company borrowed by what the owners put in and left in. Equity is total assets minus total liabilities: the slice of the company nobody else has a claim on. A ratio of 0.50 means fifty cents borrowed for every dollar owned. A ratio of 2.00 means two dollars borrowed for every dollar owned, and a bad year now threatens the lenders' money as well as the owners'.

The disagreement is over the numerator. Interest-bearing debt counts only money that was borrowed and carries interest: loans, bonds, the short-term borrowings line. Total liabilities counts everything the company owes anyone, including supplier invoices and accrued wages, which carry no interest and no repayment schedule.

On Northlight, run both.

Definition	Numerator	Debt to equity
Interest-bearing debt	125,000 + 1,065,000 = 1,190,000	0.70
Total liabilities	700,000 + 1,065,000 = 1,765,000	1.04

Both divide by the same $1,700,000 of equity. One says the company is modestly borrowed. The other says it owes slightly more than it owns. The gap is the $575,000 of payables and accruals that carry no interest, and the wider definition is 48% higher on this company.

Neither is wrong. A credit analyst usually wants interest-bearing debt, because that is what has to be serviced on a schedule. An accounting textbook usually means total liabilities. Pick one, name it in the column header, and never compare a ratio built one way against a ratio built the other. Ridgeway, for the record, is 0.48 on the narrow definition and 0.82 on the wide one.

5. The query, and the division that returns 1

In SQL, current assets of 1,260,000 divided by current liabilities of 700,000, both stored as whole numbers. What comes back?

Not 1.80. Whole number divided by whole number gives a whole number in most databases, so the 0.80 is discarded. This is nastier here than in a percentage, because 1 is a perfectly plausible current ratio. It sits exactly on the line between covered and not covered, so a reader has no reason to doubt it.

Watch what it does to all three ratios on Northlight.

What you write	Returns	The truth
`current_assets / current_liabilities`	1	1.80
`(current_assets - inventory) / current_liabilities`	1	1.10
`total_debt / equity`	0	0.70
`ROUND(current_assets / current_liabilities, 2)`	1.0	1.80

The last row is the dangerous one. ROUND makes the output look like a decimal calculation, so the column reads 1.0 and 1.0 and 0.0 down the page and nothing suggests the arithmetic never happened. On Ridgeway the same bug returns a current ratio of 2 and a quick ratio of 0, which turns "0.60, and it is short" into "0, and it has nothing", a different and equally wrong story.

The fix is one character in three places. Multiply by 1.0 before dividing, and the whole expression goes decimal.

SELECT company,
       ROUND(1.0 * current_assets / current_liabilities, 2)               AS current_ratio,
       ROUND(1.0 * (current_assets - inventory) / current_liabilities, 2) AS quick_ratio,
       ROUND(1.0 * total_debt / equity, 2)                                AS debt_to_equity
FROM balance_sheet
WHERE as_of = '2024-12-31'
ORDER BY company;

That returns 1.80 / 1.10 / 0.70 for Northlight and 2.40 / 0.60 / 0.48 for Ridgeway. The same rule catches every ratio you will ever write in SQL, and it is worked from the other direction in gross vs operating vs net margin, where the same division returns 0 instead of 1.

Now the WHERE as_of line, which matters more than it looks. A balance sheet table usually holds several snapshots, one per quarter or per month. Leave the filter off and SUM quietly adds four dates together. Northlight's four 2024 snapshots summed give a current ratio of 1.92; the year-end figure is 1.80. Both are plausible, only one is the company's current ratio, and nothing in the output tells you which you got.

Picture your own company's balance-sheet table. How many dates are in it, and does the query you inherited filter to one of them? That check takes ten seconds and it is worth doing before you trust any ratio you did not write yourself.

6. Benchmarks, and why a high ratio can be bad news

A company's current ratio is 6.0. Name a reason that might not be good.

Idle money. Assets sitting in cash or unsold stock are assets not being used to grow anything. A current ratio far above what the business needs usually means cash the company has not found a use for, or inventory it cannot shift. Neither is a crisis, and neither is a strength.

The usual comfortable band is roughly 1.5 to 3.0 for the current ratio and at or above 1.0 for the quick ratio, but treat those as starting points rather than rules. A supermarket collects cash at the till and pays its suppliers weeks later, so it runs happily below 1.0 and always has. A shipbuilder holds enormous inventory for years and runs far higher. The band belongs to the industry, not to accounting.

The same goes for leverage. Debt to equity above about 2.0 is a flag for most companies, and utilities, airlines and property firms run above it as a matter of course, because they own predictable assets that lenders are happy to lend against. Comparing an airline's leverage to a software company's tells you what industry they are in, not which is better run.

Three comparisons are worth more than any published band.

The same company over time. This is the one that always works, because everything else is held constant.
A named competitor of similar size, in the same industry, on the same date.
The covenant. If the company has a loan, the agreement almost certainly names a minimum current ratio or a maximum debt-to-equity ratio in writing. That number beats every benchmark on the internet, because breaching it has consequences on a specific date.

Time is where the story usually is. Here are Northlight's four 2024 snapshots, with the three inputs each ratio comes from, so the trend can be checked the same way the year-end figures were.

As of	Current assets	Inventory	Receivables	Current liabilities	Current ratio	Quick ratio
2024-03-31	1,130,000	300,000	380,000	545,000	2.07	1.52
2024-06-30	1,170,000	360,000	420,000	595,000	1.97	1.36
2024-09-30	1,220,000	430,000	450,000	653,000	1.87	1.21
2024-12-31	1,260,000	490,000	470,000	700,000	1.80	1.10

Check the March row against the December one you already worked. 1,130,000 minus 300,000 of inventory is 830,000, over 545,000 of bills, which is 1.52. The quick ratio here is the current-assets-minus-inventory version from section three, the same one used throughout this page.

Read as one date, 1.80 and 1.10 are fine. Read as four, the quick ratio has fallen every quarter, from 1.52 to 1.10, and it is heading for the 1.0 line. Inventory grew from $300,000 to $490,000 across the year, up 63%, while receivables grew from $380,000 to $470,000, up 24%. Northlight is quietly turning into Ridgeway, and no single snapshot could have told you that.

This is also where the missing filter from section five shows its teeth. Summed across all four dates, current assets are $4,780,000 against $2,493,000 of current liabilities, which is the 1.92 a query with no WHERE as_of returns. It is not any quarter's ratio, it is not the average of the four, and it is the one number in this table that describes nothing that ever happened.

7. The full before and after

Same two balance sheets, two ways of reporting them.

Before

"Ridgeway current ratio 2.40, Northlight 1.80. Both above the 1.5 threshold. No liquidity concerns." Every number is right, the threshold is a real one, and the conclusion is backwards. A reader has no way to see that one of these companies has $300,000 of spendable assets against $500,000 of bills, because the report never separated the spendable part from the rest.

After

Measure	Northlight	Ridgeway	Read
Current ratio	1.80	2.40	Both above 1.5
Quick ratio	1.10	0.60	Only one clears 1.0
Quick assets	770,000	300,000	The dollars behind the ratio
Current liabilities	700,000	500,000	What is due within the year
Gap	+70,000	-200,000	Ridgeway must sell stock to pay
Debt to equity (interest-bearing)	0.70	0.48	Ridgeway borrowed less
Debt to equity (total liabilities)	1.04	0.82	Same companies, wider definition

Two additions do all the work. The quick ratio splits the pile, and the gap row converts the ratio back into dollars so a reader does not have to. The definition of debt is named in the row label rather than assumed. And the finding is now visible without any expertise at all: the company that borrowed less is the one that might not make its payments.

8. Edge cases: a snapshot is easy to move

It is 30 December. Name one thing a company could do that day to improve its current ratio without changing anything about the business.

Pay a bill. Take $200,000 of cash and settle $200,000 of accounts payable on the last working day of the year. Current assets fall to $1,060,000, current liabilities fall to $500,000, and the current ratio goes from 1.80 to 2.12. The company is no healthier. It has less cash than it did that morning. Because both sides of the fraction shrink by the same amount, and the top was already bigger, the ratio improves. This has a name in the trade, window dressing, and it is why a lender looks at several dates rather than one. Northlight's cash after that payment would be $60,000 of the $260,000 it started with, which is the part a ratio never shows you.

A ratio is a snapshot and an income statement is a film. Mixing them needs care. Ratios that put a balance-sheet number over an income-statement number, like inventory turnover, conventionally use the average of the opening and closing balance rather than the closing one, precisely because one date does not represent a year. If you build one of those, say which convention you used.

Equity can be negative, and then the ratio is meaningless. A company that has lost more over its life than owners put in has negative equity, and debt to equity comes out negative. A negative leverage ratio is not low leverage, it is a company whose liabilities exceed its assets. Guard it in the query and report the words instead of the number.

Not every current liability is equally urgent. A payable due in eleven months and one due next Tuesday both sit in the same total. The ratio treats them identically, and the company's bank account does not. When liquidity is genuinely tight, the ratio stops being the right tool and a week-by-week cash forecast starts being one.

Receivables are only as good as the customers. The quick ratio trusts every dollar of receivables at face value. If a third of them are ninety days overdue, the real quick ratio is lower than the computed one. An ageing report sits next to this analysis, not after it.

Why this works

The reason a single ratio is not enough is a measurement problem, not an accounting one. Any single indicator used to judge performance tends to be managed toward rather than improved on, an effect Campbell set out from social program evaluation (Campbell, 1979, Evaluation and Program Planning , 2(1), 67–90) and Goodhart put in one line about monetary targets (Goodhart, 1975, reprinted in Monetary Theory and Practice , Macmillan, 1984, 91–121). The 30 December payment in section eight is that effect on a balance sheet: the number moved, the thing it measures did not. Two ratios that can disagree, read across four dates, is the cheapest defence available.

Ratio analysis of this kind is also older and better tested than it looks. Beaver's study of 79 failed companies against 79 matched survivors found that balance-sheet ratios separated the two groups up to five years before failure, with cash-flow and debt ratios among the strongest single predictors (Beaver, 1966, Journal of Accounting Research , 4, 71–111). Altman's multi-ratio model, built on 66 manufacturers, classified 95% of them correctly one year out (Altman, 1968, The Journal of Finance , 23(4), 589–609). Neither found any one ratio sufficient on its own, which is the finding this page is built on.

The question at the top of each section is deliberate. Attempting an answer before receiving one improves learning of that specific material across sixty-four studies (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Guessing which asset is slowest to become cash, before section three said inventory, is why you will still know it in an interview.

Using this on your own numbers

Rebuilding every ratio report in the company is a slog and nobody thanks you for it. Do this instead, in order.

Add the quick ratio wherever a current ratio already appears. One column, one subtraction, and it is the column that would have caught Ridgeway.
Check the division is decimal. If any ratio in your report is a whole number like 1, 2 or 0, you have integer division. Section five has the fix.
Confirm the query filters to oneas_of date. Then put four dates side by side, because the trend is where the story was on this page.
Name the debt definition in the column header. "D/E (interest-bearing)" costs three words and removes an argument you would otherwise have every quarter.
Add a dollar column next to every ratio. Quick assets and current liabilities in dollars, and the gap between them. A ratio persuades nobody; $200,000 short does.
Find the loan covenant and write its number at the top of the report. If there is a minimum current ratio in a credit agreement, that is the only threshold on the page that has consequences.

One optional drawing, if there is paper within reach. Draw the two columns from the top of this page from memory: a stack of current assets for each company, a dashed line for the bills due, and the split between the part you can spend and the inventory above it. Mark which solid block reaches its line. If both of yours clear it, you have drawn the current ratio rather than the quick ratio, and the page is still open.

This page is one of five. The Financial analysis set on the guides index is this page plus Gross vs Operating vs Net Margin, Contribution Margin and Break-Even, Net Present Value (NPV) and Budget vs Actual Variance. They share a worked company, so the numbers carry across. The index also holds every other how-to: SQL, Excel, Tableau, and the working habits around them.

The whole thing on one screen

Cover the right column. Work down the left and say each answer before you look.

Concept	What it is
Liquidity	Can we pay what is due soon with what becomes cash soon.
Leverage	How much of the company was paid for with borrowed money.
Current	Within one year. That is the whole meaning of the word.
Current ratio	Current assets ÷ current liabilities. Northlight 1.80.
Quick ratio	The same test with inventory taken out. Northlight 1.10.
Why drop inventory	It is the slowest current asset to become cash, and it might not.
The reversal	Ridgeway: current 2.40, quick 0.60, and $200,000 short of its bills.
Two quick-ratio formulas	Current assets minus inventory gives 1.10. Cash plus receivables gives 1.04.
Debt to equity	Borrowed ÷ owned. 0.70 on interest-bearing debt for Northlight.
The debt argument	Total liabilities instead gives 1.04 on the same balance sheet.
The integer trap	1260000 / 700000 returns 1. Write `1.0 *` before the division.
The missing filter	No `WHERE as_of` summed four snapshots and gave 1.92 instead of 1.80.
Comfortable bands	Current 1.5 to 3.0, quick at or above 1.0, D/E under 2.0. Industry decides.
Too high	Idle cash or unsold stock. Not a strength.
Window dressing	Paying $200,000 on 30 December moved 1.80 to 2.12 and changed nothing.
Negative equity	D/E goes negative. That is not low leverage, it is insolvency.
The deliverable	The ratio, the dollars behind it, and the trend across four dates.

The one habit to keep. Keep this one and you can let the rest go. Never report a liquidity ratio without the dollar gap beside it. "Quick ratio 0.60" is a number people nod at. "$300,000 of spendable assets against $500,000 of bills" is a number people act on, and it is the same fact. If the numbers fight back in a way this page does not cover, there is a general diagnosis loop for being stuck.

A question back to you, and I do want the answers. The first ratio table I built had a column header that just said "D/E", and two people in the room were reading it as two different numbers for ten minutes before anyone noticed. What is the shortest column header you have seen cause the longest argument?

References

Beaver, W. H. (1966). Financial ratios as predictors of failure. Journal of Accounting Research , 4, 71–111.
Altman, E. I. (1968). Financial ratios, discriminant analysis and the prediction of corporate bankruptcy. The Journal of Finance , 23(4), 589–609.
Campbell, D. T. (1979). Assessing the impact of planned social change. Evaluation and Program Planning , 2(1), 67–90.
Goodhart, C. A. E. (1975). Problems of monetary management: The U.K. experience. Reprinted in Monetary Theory and Practice: The U.K. Experience (pp. 91–121). Macmillan, 1984.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.

Originally published on Analyst Prep Kit: Current Ratio vs Quick Ratio, and Debt to Equity

If it was useful: Buy Me a Coffee.

Gross vs Operating vs Net Margin: What Each One Tells You

Michael Nocito — Mon, 07 Sep 2026 13:00:04 +0000

By Michael Nocito, data analyst · Published August 11, 2026

By the end of this page you can look at an income statement and say, in one sentence, which layer of the business is losing the money. You will know what each of the three margins measures, why they always step down in order, the SQL that computes all three in one pass, and the two ways that SQL quietly returns the wrong number.

Try this before you read another word. Take any two periods of your company's numbers. Work out what percent revenue grew, then what percent profit grew. If profit grew slower, the business got bigger and kept less of each dollar, and the three margins tell you which layer ate the difference.

In one line: a margin is a profit line divided by revenue. Each one subtracts one more layer of cost, so gross is always at least operating, and operating is always at least net.

The stepping-down is the whole shape of it, so it gets the picture.

The original carries a diagram here. In words: A single horizontal bar representing one dollar of revenue, drawn four times, one row under the next, each row shorter than the one above it. The top row is the full bar, labeled revenue, running the whole width. The second row is 60 percent as long and labeled gross; the piece missing from its right-hand end is drawn as a hatched block labeled cost of goods. The third row is 22 percent as long and labeled operating; the piece missing from the second row is a hatched block labeled operating expense. The fourth row is 14 percent as long and labeled net; the piece missing from the third row is a hatched block labeled interest and tax. Each hatched block sits directly to the right of the bar it was taken from, so the four rows form a descending staircase with the removed costs stacking to the right. A dashed vertical line marks the left edge shared by every bar, showing that all four are measured from the same starting point, which is revenue. The picture is the reason the three margins can never cross: each one starts from the row above it and can only get shorter.

Every number on this page is verified, and you can check them. The worked example is one small company's two most recent years, shown in full below. Every margin, growth rate, subtotal and monthly figure was computed in SQLite and cross-checked in pandas before it went on the page, so you can check any cell by hand and it will agree.

1. What a margin is, and why divide by revenue at all

Two years of profit, $538,000 then $560,000. Say whether that company had a good year, and say what you would need to know to be sure.

A margin is a profit line divided by revenue, written as a percent. That is the entire definition. If revenue is $4,000,000 and net income is $560,000, the net margin is 560,000 divided by 4,000,000, which is 0.14, which is 14%.

The reason to divide is that raw profit dollars cannot be compared to anything. A bigger year makes more profit almost automatically, so "profit went up" tells you the company got bigger, not that it got better. Dividing by revenue converts the number into cents kept per sales dollar, and cents kept per dollar can be compared to last year, to the plan, and to a competitor ten times the size.

One warning about the word "kept," because it is doing less work than it sounds like. A margin of 14% does not mean 14 cents of every dollar is in the bank. It means 14 cents survived on paper, and paper and cash part company constantly. A customer who has not paid yet still counts as revenue and still counts as profit. A company can post its best margin ever and miss payroll in the same month. What a business can actually pay is a different question with different arithmetic, and it lives on the balance sheet rather than here: the current ratio and the quick ratio are the two numbers that answer it.

Here is the same idea in one line you can hold on to. Profit answers "how much did we make." A margin answers "how much of what came in did we keep, on paper." Those are different questions, and only the second one survives a change in size.

2. The three margins, and the layer each one accuses

If a company's product sells profitably but the company loses money overall, which of the three margins would still look healthy?

An income statement is a cascade. Revenue at the top, then costs subtract in layers, and each subtotal along the way is a profit line worth dividing by revenue. Three of those subtotals matter most.

Gross margin is gross profit divided by revenue, where gross profit is revenue minus cost of goods sold. Cost of goods sold is what it costs to make or deliver the thing you sold: materials, the people who build it, shipping. Gross margin is the product's own economics. It moves when you change your price or when your suppliers change theirs, and almost nothing else touches it.

Operating margin is operating income divided by revenue, where operating income is gross profit minus operating expense. Operating expense is the cost of running the company rather than making the product: sales salaries, rent, software, marketing, the finance team. Operating margin is the core business. It moves when overhead grows faster than sales.

Net margin is net income divided by revenue, and net income is what is left after everything, including interest on borrowed money and tax. Net margin moves on how much debt the company carries and what it pays the government, neither of which has much to do with how well the product sells.

So each margin removes one more layer, and that is why they always fall in the same order: gross is at least operating, and operating is at least net. When a company's product is profitable but the company is not, gross margin stays healthy and the collapse shows up below it. The margin that stops looking good is the layer that owes you an explanation.

3. The worked income statement, two years side by side

Revenue is about to grow by a third. Guess whether net margin goes up or down, and write your guess down.

Here is the company. It sells coffee equipment, it books everything in whole dollars, and these are its last two fiscal years.

Line	2023	2024
Revenue	3,000,000	4,000,000
Cost of goods sold	1,110,000	1,600,000
Gross profit	1,890,000	2,400,000
Operating expense	1,116,000	1,520,000
Operating income	774,000	880,000
Interest	60,000	120,000
Tax	176,000	200,000
Net income	538,000	560,000

Check one column by hand to trust the rest. 2024: 4,000,000 minus 1,600,000 is 2,400,000 gross profit. Minus 1,520,000 of operating expense is 880,000. Minus 120,000 of interest and 200,000 of tax is 560,000.

Now the same two years as margins.

Margin	2023	2024	Change
Gross	63.0%	60.0%	-3.0 points
Operating	25.8%	22.0%	-3.8 points
Net	17.9%	14.0%	-3.9 points

Both tables describe the same two years. The first one says the company had its best year. The second one says the company had its worst year in a while. Revenue grew 33.3%, gross profit grew 27.0%, operating income grew 13.7%, and net income grew 4.1%. Every line went up, and every line went up slower than the one above it.

The one sentence this table earns: the company grew a third bigger and now keeps 14.0 cents of every dollar instead of 17.9. Put the shortfall in dollars and it stops being abstract. At last year's net margin, $4,000,000 of revenue would have produced $717,333 of net income. It produced $560,000. The difference is $157,333, which is more than a quarter of the profit the company actually made.

Say why net margin can fall while net income rises, in your own words, before reading on. If you can say it, the rest of this page is arithmetic.

4. The query, and the division that returns 0

In SQL, 2400000 / 4000000 where both columns are whole numbers. What comes back?

Zero. Not 0.6. Most databases follow the rule that a whole number divided by a whole number is a whole number, so the 0.6 is thrown away and you get 0. Multiply that 0 by 100 afterwards and you get 0. The query runs, the column fills with zeros, and nothing anywhere says an error happened.

The fix is to make one side of the division a decimal, and to do it before the division rather than after. Writing 100.0 * at the front of the expression turns the whole calculation decimal from that point on.

SELECT fiscal_year,
       ROUND(100.0 * (revenue - cogs) / revenue, 1)
         AS gross_margin_pct,
       ROUND(100.0 * (revenue - cogs - operating_expense) / revenue, 1)
         AS operating_margin_pct,
       ROUND(100.0 * (revenue - cogs - operating_expense - interest - tax) / revenue, 1)
         AS net_margin_pct
FROM income_statement
ORDER BY fiscal_year;

Run against the two years above, that returns 63.0 / 25.8 / 17.9 and 60.0 / 22.0 / 14.0. Here is the same expression written four ways, all on the 2024 row, so you can see exactly where the decimal has to sit.

What you write	What comes back	Right?
`gross_profit / revenue * 100`	0	No. The division happens first and rounds to 0.
`100 * gross_profit / revenue`	60	Right this time, wrong in general. It rounds the answer down to a whole percent.
`100.0 * gross_profit / revenue`	60.0	Yes.
`ROUND(100.0 * gross_profit / revenue, 1)`	60.0	Yes, and it stops the display carrying twelve decimal places.

Row two is the one that catches people, because on this data it returns 60 and 60 is correct. It is correct by luck. Multiply first and the result is a whole number of percent, so 63.0% would come back as 63 and a genuine 22.4% would come back as 22. You would never see the missing four tenths. The wider version of this rule shows up in every growth calculation, and month-over-month growth in SQL works the same trap on a different formula.

One more note on ROUND. It does not protect you from integer division, because by the time ROUND sees the value the 0 has already happened. ROUND(0, 1) is 0.0, which looks like a decimal calculation and is not one.

5. Averaging monthly margins loses 3.1 points

You have twelve monthly operating margins. Somebody asks for the year's operating margin. Is averaging the twelve the right move?

No, and here is the size of the mistake. This is the same 2024 as above, split into the twelve months it was made of. Cost of goods runs at 40% of each month's revenue, and operating expense is a fixed $110,000 base plus 5% of revenue, which is what a real cost structure looks like: some of it scales with sales and some of it arrives whether you sell anything or not.

Month	Revenue	Cost of goods	Operating expense	Operating income	Operating margin
2024-01	180,000	72,000	119,000	-11,000	-6.1%
2024-02	195,000	78,000	119,750	-2,750	-1.4%
2024-03	250,000	100,000	122,500	27,500	11.0%
2024-04	300,000	120,000	125,000	55,000	18.3%
2024-05	330,000	132,000	126,500	71,500	21.7%
2024-06	360,000	144,000	128,000	88,000	24.4%
2024-07	340,000	136,000	127,000	77,000	22.6%
2024-08	315,000	126,000	125,750	63,250	20.1%
2024-09	350,000	140,000	127,500	82,500	23.6%
2024-10	400,000	160,000	130,000	110,000	27.5%
2024-11	480,000	192,000	134,000	154,000	32.1%
2024-12	500,000	200,000	135,000	165,000	33.0%

Those twelve rows add to exactly the annual numbers: $4,000,000 of revenue, $1,600,000 of cost of goods, $1,520,000 of operating expense. Now the two ways to answer "what was our operating margin this year."

-- Wrong. Averages twelve percentages as if each month were the same size.
SELECT ROUND(AVG(100.0 * (revenue - cogs - operating_expense) / revenue), 1)
FROM monthly_pl;                                     -- 18.9

-- Right. Adds the money first, divides once.
SELECT ROUND(100.0 * SUM(revenue - cogs - operating_expense) / SUM(revenue), 1)
FROM monthly_pl;                                     -- 22.0

Three point one percentage points apart, from the same twelve rows. On $4,000,000 of revenue that gap is $124,000 of profit that the first query cannot see.

The reason is that January's revenue of $180,000 and December's $500,000 count equally in an average of percentages, and they are not equal amounts of business. January lost money, at an operating margin of -6.1%, on less than half the revenue of a good month. Averaging lets that small bad month push the answer down as hard as a large good month pushes it up.

The rule that fixes it: add the money, then divide once. A margin is a ratio, and ratios are not averaged, they are recomputed on the totals. This is the same reasoning that makes a class's overall pass rate different from the average of each teacher's pass rate, and it is worth recognising by name, because it shows up whenever a rate is rolled up from parts of different sizes.

Picture running that AVG query on your own company's monthly figures. Do you have a January, a quiet month with the same fixed costs as a busy one? If you do, your averaged margin is already wrong, and it is wrong in the optimistic direction whenever the quiet months are the profitable ones.

6. Reading a margin: the four benchmarks

A company reports a 2% net margin. Good or bad?

Unanswerable, and that is the point. Two percent is healthy for a grocery chain and alarming for a software company. A margin on its own is a number with no verdict attached, and it becomes an analysis only when you put it next to something. There are four somethings, and they answer different questions.

Against its own past. Same company, earlier period. This is the one that always works, because the company is its own control: same products, same customers, same accounting. Our example gives 17.9% falling to 14.0%, and that comparison needs no outside information at all.

Against the plan. The margin the budget assumed. A miss against plan is a different conversation from a fall against last year, because someone chose the plan number and can be asked why. Budget vs actual variance is the full method for that comparison.

Against the industry. Grocery retail runs on net margins of a few percent because it turns over enormous volume at tiny per-item profit. Packaged software can run above 20% because the cost of the next copy is close to nothing. A margin outside its industry's usual band is either a real advantage or an accounting difference, and it is worth finding out which before you present it.

Against a specific peer. The most useful comparison, and the one that points at a cause. Take two companies with the same gross margin.

Company	Revenue	Gross margin	Operating margin	Net margin
Ours	4,000,000	60.0%	22.0%	14.0%
Peer	4,000,000	60.0%	29.0%	23.5%

Identical products, as far as gross margin can tell. Nine and a half points apart at the bottom. The cascade says exactly where to look: the two companies spend $1,520,000 and $1,240,000 on operating expense, a $280,000 gap, and below that our interest and tax take $320,000 against their $220,000. Same product economics, more overhead, more debt. That is a finding, and it took no information beyond two income statements.

7. The full before and after

Same company, same two years, two ways of reporting it.

Before

"Revenue up $1,000,000 to $4,000,000. Gross profit up $510,000. Net income up $22,000 to $560,000. A record year on every line." Every sentence is true. A reader gets no way to tell that the company is keeping less of what it earns, because nothing on the page is measured against anything. The $22,000 improvement even reads as good news, which is the part that costs you credibility three months later.

After

Measure	2023	2024	Read
Revenue	3,000,000	4,000,000	+33.3%
Gross margin	63.0%	60.0%	-3.0 pts, pricing or input costs
Operating margin	25.8%	22.0%	-3.8 pts, overhead grew faster than sales
Net margin	17.9%	14.0%	-3.9 pts, interest doubled to 120,000
Net income	538,000	560,000	+4.1%, and $157,333 below last year's margin

Same numbers, plus one division per row. The report now names three separate causes and sizes each one, and the last row converts the whole thing into the only unit a manager acts on, which is dollars. The claim on the top of the email writes itself: we grew 33.3% and net margin fell 3.9 points, which cost $157,333 against holding last year's rate.

8. Edge cases that break the cascade

Net margin comes out higher than operating margin. Name something that could do that.

A one-off gain below the line. Sell a building for $400,000 and net income becomes $960,000 on the same $4,000,000 of revenue. Net margin is 24.0% and operating margin is 22.0%, so the cascade is upside down. Nothing is broken. Money arrived from somewhere that is not the business, and next year it will not. Whenever net sits above operating, look for a sale, a legal settlement, or a tax credit, and say so in the same sentence as the number.

Cost of goods and operating expense are not defined the same way everywhere. Whether shipping, customer support, or the cloud bill sits above or below the gross-profit line is a choice each company makes. Two companies can have genuinely identical economics and gross margins eight points apart. That does not make either wrong, and it does make a gross-margin comparison between two companies weaker than it looks. The comparison inside one company across time is always the safer one.

A negative revenue or a zero revenue makes the percent meaningless. A month with no sales divides by zero, and depending on the database that is an error or a null. Guard it: CASE WHEN revenue > 0 THEN 100.0 * profit / revenue END. A blank is honest. A zero is a lie, because 0% margin means you broke even, not that you had no revenue.

Points are not percents, and mixing them up is common in writing. Net margin fell from 17.9% to 14.0%. That is a fall of 3.9 percentage points. It is also a fall of about 22% of the margin , because 3.9 is close to a fifth of 17.9. Both are true, they are wildly different numbers, and only one of them belongs in a sentence at a time. Say "points" when you mean the subtraction.

Margins hide mix. A company selling two products at 80% and 40% gross margin has an overall gross margin somewhere in between, and that overall number moves whenever the sales mix moves, even if neither product changed at all. If your gross margin dropped and no price changed, check whether you simply sold more of the cheaper thing.

Why this works

The rule in section five, add the money then divide once, is not a style preference. Combining groups of different sizes can reverse the direction of a rate, an effect first set out formally by Simpson (Simpson, 1951, Journal of the Royal Statistical Society, Series B , 13(2), 238–241) and traced to Yule half a century earlier. Blyth's later treatment gives the plainest statement of the danger: an association that holds in every subgroup can vanish or flip when the subgroups are pooled (Blyth, 1972, Journal of the American Statistical Association , 67(338), 364–366). Averaging twelve monthly margins pools twelve groups of very different sizes while pretending they are equal, which is exactly the condition those papers warn about, and the 3.1-point gap on this page is the mild version of it.

The question at the top of each section is not decoration. Attempting an answer before you are given one improves learning of that specific material, an effect measured across sixty-four studies of self-explanation prompts (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Guessing what 2400000 / 4000000 returns before section four told you is why you will still remember it next month. The same evidence says the cheat sheet below works best covered up and recalled rather than reread (Roediger & Karpicke, 2006, Psychological Science , 17(3), 249–255).

Using this on your own numbers

Retrofitting margins onto every report you have inherited is a slog, and the old ones have owners who like them. Do this instead, in order.

Add three columns to the report you already own. Gross, operating and net margin next to the dollars. Three divisions, and the dollars stay exactly where they are so nobody has to relearn the page.
Check the division is decimal. Run one row by hand. If a margin comes back as 0, 60 or 1, you have integer division, and section four has the fix.
Put last year's margin in the next column. This is the comparison that always works and it needs no outside data.
Search your codebase forAVG( next to a division. Every one of those is a rate being averaged, and each one needs checking against the sum-then-divide version. Fix the ones that disagree by more than a rounding step.
Convert one margin change into dollars every cycle. Last year's rate times this year's revenue, minus this year's actual. That single number is what makes a manager act.
Write the industry band down once, somewhere permanent. Your own sector's usual range, in a comment at the top of the query. Six months from now nobody will remember whether 14% was good.

If you have paper nearby, one optional drawing locks the core idea in. Draw the staircase from the top of this page from memory: four bars measured from the same left edge, each shorter than the one above, with the removed cost hatched beside it. Label the three layers you strip. If your bars come out the same length, or in the wrong order, the page is still open.

This page is one of five. The Financial analysis set on the guides index is this page plus Current Ratio vs Quick Ratio, Contribution Margin and Break-Even, Net Present Value (NPV) and Budget vs Actual Variance. They share a worked company, so the numbers carry across. The index also holds every other how-to: SQL, Excel, Tableau, and the working habits around them.

The whole thing on one screen

Cover the right column and work down the left. Saying each answer out loud before you look is what makes it stick.

Concept	What it is
Margin	A profit line divided by revenue. Cents kept per sales dollar.
Kept, on paper	A 14% margin is not 14 cents in the bank. Unpaid customers still count as profit.
Why divide	Profit dollars grow with size. A margin survives a change in size.
Gross margin	(Revenue − cost of goods) ÷ revenue. The product's own economics.
Operating margin	Operating income ÷ revenue. The core business, before financing and tax.
Net margin	Net income ÷ revenue. What is left after absolutely everything.
The cascade rule	Gross ≥ operating ≥ net. Each strips one more layer of cost.
Reading the cascade	The first margin that stops looking healthy names the layer to investigate.
The integer trap	2400000 / 4000000 returns 0. Put `100.0 *` at the front, before the division.
Why ROUND does not help	The 0 already happened. ROUND(0, 1) is 0.0 and looks fine.
The averaging trap	AVG of twelve monthly margins gave 18.9%. The truth was 22.0%.
The rule that fixes it	Add the money, then divide once. SUM(profit) / SUM(revenue).
Four benchmarks	Its own past, the plan, the industry, a named peer.
Net above operating	Money arrived from outside the business. Find it and say so.
Points vs percent	17.9 to 14.0 is 3.9 points, and also about 22% of the margin. Pick one.
Mix	Selling more of the cheaper product moves the blended margin with no price change.
The deliverable	One sentence: the margin, the direction, and what it cost in dollars.

The one habit to keep. One habit, if you keep only one. Never average a rate. Add the numerators, add the denominators, divide once. That one habit covers margins, conversion rates, defect rates, retention and every other percentage you will ever roll up. If a query fights back in a way this page does not cover, there is a general diagnosis loop for being stuck.

One last thought, and I would genuinely like other people's answers. The first margin I ever reported was averaged across months, and it was wrong by about three points for four straight quarters before anyone checked. Nobody caught it because it was in the right neighbourhood every single time. What is the wrongest number you have shipped that survived because it looked reasonable?

References

Simpson, E. H. (1951). The interpretation of interaction in contingency tables. Journal of the Royal Statistical Society, Series B , 13(2), 238–241.
Blyth, C. R. (1972). On Simpson's paradox and the sure-thing principle. Journal of the American Statistical Association , 67(338), 364–366.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science , 17(3), 249–255.

Originally published on Analyst Prep Kit: Gross vs Operating vs Net Margin: What Each One Tells You

If it was useful: Buy Me a Coffee.

Contribution Margin and Break-Even: The Fixed Cost That Does Not Belong in the Unit

Michael Nocito — Sun, 06 Sep 2026 13:00:31 +0000

By Michael Nocito, data analyst · Published August 11, 2026

By the end of this page you can answer three questions that come up constantly and get answered wrong: does one more sale make money, how many sales cover the fixed base, and what a discount actually costs. You will know the contribution margin formula, the break-even formula, the SQL for both, and the one mistake that turns a 750-unit answer into a 12,000-unit answer.

Start here, on one product, today. Take one product and split its costs into two piles: costs that happen because you sold that unit, and costs that would have happened anyway. Subtract only the first pile from the price. That number is what each sale is really worth, and almost nobody has it to hand.

Two formulas, and that is the lot. Contribution margin is price minus variable cost. Divide the fixed costs by it and you get break-even, the number of units where the business stops losing money.

Where the fixed costs sit is the thing everybody gets wrong, so it gets the picture.

The original carries a diagram here. In words: Two panels, each showing a single tall bar that represents the selling price of one unit, split into stacked segments. In the left panel, marked with a check, the price bar has just two segments: a hatched grey lower segment labeled variable, taking up sixty percent of the height, and a solid accent-colored upper segment labeled contribution, taking up the other forty percent. To the right of that bar, and deliberately not touching it, sits a separate wide low box with a dashed outline, labeled fixed. It is drawn wide and short so it reads as a container for a month of overhead rather than as anything belonging to one unit. An arrow runs from the contribution segment down into that box, and the bottom of the box is partly filled with small solid tiles, each one the same color as the contribution segment. The tiles say that the contribution from each sale is what fills the fixed box, and that filling it up is what break-even means. In the right panel, marked with a cross, the same price bar has been divided three ways instead of two: the same hatched variable segment at the bottom, then a segment above it labeled fixed share drawn with the same dashed outline as the fixed box in the left panel, so the eye can see it is that box carved up and pushed inside the unit, and finally a solid accent-colored sliver at the very top that is only a sixteenth as tall as the contribution segment in the left panel. There is no separate fixed box standing beside the right-hand bar, because it has been dissolved into it. The picture shows that the same product yields a large contribution or a tiny one depending only on whether the fixed cost was allowed inside the bar.

Every number on this page is verified, and you can check them. The worked example is one product's month, six inputs, shown in full below. Every contribution figure, break-even, discount scenario and leverage number was computed in SQLite and cross-checked in pandas before it went on the page, so you can check any line on a calculator and it will agree.

1. Fixed and variable, and the test that sorts them

Your factory rent is $60,000 a month. You sell one more grinder. How much more rent do you pay?

None. That is the whole test, and it is the only test that matters here. Ask what happens to the cost if you sell exactly one more unit. If the cost goes up, it is variable. If it does not move, it is fixed.

Variable costs on a grinder: the parts, the packaging, the payment processing fee, the shipping, the piece of assembly labour that only happens because this unit exists. Fixed costs: the rent, the salaried staff, the software subscriptions, the insurance. Those arrive whether you sell nine hundred units or none.

Two things make this harder than it sounds, and both are worth knowing before you sort a real cost list.

Fixed only holds inside a range. Rent is fixed until you need a second warehouse. A salaried team is fixed until the volume needs another hire. Accountants call that range the relevant range, and the honest version of "fixed" is "fixed across the volumes we are actually considering." Break-even arithmetic is only valid inside it.

Some costs are both. A phone bill with a monthly line rental and a per-minute charge is part fixed, part variable. A salesperson on base plus commission is the same shape. Split those two ways rather than forcing them into one pile, and if you cannot split them, say which way you rounded.

What makes this split worth the trouble is that it answers a question the income statement cannot. A profit and loss account sorts costs by what they are: cost of goods, then operating expense. That tells you nothing about what one more sale does, because both of those buckets contain a mix of fixed and variable. Sorting by behaviour instead of by category is the entire move.

2. Contribution margin, per unit and as a ratio

A grinder sells for $200 and costs $120 in parts, packaging and shipping. How much of that $200 is left to pay the rent?

Eighty dollars. That is the contribution margin: price minus variable cost, per unit. The name says exactly what it is. It is the amount each sale contributes toward the fixed costs, and once those are covered, toward profit.

Notice what it is not. It is not profit per unit. There is no such thing as profit per unit until you know how many units there are, because the fixed costs have to be paid out of the pile of contributions and the size of the pile depends on the count. Contribution margin is the only per-unit number that is true at every volume, which is why it is the one to build on.

The same thing as a percentage is the contribution margin ratio: contribution divided by price. Here that is 80 divided by 200, which is 40%. Forty cents of every sales dollar survives the variable costs.

Use the per-unit version when you are counting units and the ratio when you are working in revenue. A sales manager forecasting units wants $80. A finance director looking at a revenue plan wants 40%, because 40% of any revenue number is the contribution that revenue throws off.

Here is the whole product, and everything else on this page comes out of these six numbers.

Input	Value
Price per unit	200
Variable cost per unit	120
Fixed costs per month	60,000
Units sold per month	800
Contribution margin per unit	80
Contribution margin ratio	40.0%

3. Break-even, and the worked model

Fixed costs are $60,000 a month and each unit contributes $80. Work out how many units cover the fixed costs before you read on.

Seven hundred and fifty. Break-even in units is fixed costs divided by contribution margin per unit: 60,000 divided by 80. Each sale drops $80 into the fixed-cost box, so it takes 750 of them to fill a $60,000 box, and unit 751 is the first one that adds profit.

Break-even in revenue is the same answer in dollars, and there are two ways to get it. Multiply 750 units by the $200 price to get $150,000. Or divide the fixed costs by the contribution ratio: 60,000 divided by 0.40, which is also $150,000. Use the second one when you do not have a single price, because a product line with mixed prices still has a blended contribution ratio.

Check it by building the month at 750 units. Revenue is 750 times 200, which is $150,000. Variable cost is 750 times 120, which is $90,000. Add the $60,000 of fixed cost and total cost is $150,000. Revenue minus total cost is exactly zero, which is what break-even means.

Now the real month, at 800 units.

Line	Amount
Revenue (800 × 200)	160,000
Variable cost (800 × 120)	96,000
Contribution (800 × 80)	64,000
Fixed costs	60,000
Profit	4,000

The gap between 800 units and 750 is the margin of safety: 50 units, or 6.25% of current volume. That is how far sales can fall before the product starts losing money, and 6.25% is thin. Say why a product can be profitable and still be 6.25% away from losing money, in your own words, before reading on.

Here is the same month at other volumes, which is the table worth putting in front of anyone who asks "what if sales drop."

Units	Revenue	Variable cost	Fixed cost	Total cost	Profit
0	0	0	60,000	60,000	-60,000
200	40,000	24,000	60,000	84,000	-44,000
400	80,000	48,000	60,000	108,000	-28,000
600	120,000	72,000	60,000	132,000	-12,000
750	150,000	90,000	60,000	150,000	0
800	160,000	96,000	60,000	156,000	4,000
1,000	200,000	120,000	60,000	180,000	20,000
1,200	240,000	144,000	60,000	204,000	36,000

Every row moves by $80 per unit, in both directions. That is what makes the numbers around break-even feel so violent. Ten percent more units, from 800 to 880, takes profit from $4,000 to $10,400, which is 160% more profit for 10% more sales. Ten percent fewer, from 800 to 720, takes it to a $2,400 loss, which is 160% the other way.

That multiplier has a name and a formula. Operating leverage is contribution divided by profit: 64,000 over 4,000, which is 16. One percent on volume moves profit sixteen percent, and it does it in whichever direction the volume went. High fixed costs buy you that multiplier. It is the same multiplier on the way down.

4. The 12,000-unit mistake

Accounting tells you this grinder costs $195 to make, fully loaded. It sells for $200. Work out what that says the break-even is, then hold that number.

Here is where the $195 comes from. Fixed costs of $60,000 spread across the 800 units the product currently sells is $75 a unit. Add that to the $120 of genuine variable cost and you get $195. That is a real and legitimate number for some purposes, and it is called fully loaded or fully absorbed cost, because the fixed costs have been absorbed into the unit.

Use it in a break-even calculation and everything falls apart. Price 200 minus loaded cost 195 leaves $5 of apparent margin. Divide $60,000 of fixed costs by $5 and break-even is 12,000 units.

	Correct	Fully loaded
Cost subtracted from price	120 variable only	195, variable plus fixed share
Margin per unit	80	5
Break-even units	750	12,000

Sixteen times the right answer, and 12,000 units is fifteen times the volume the product actually sells. A manager handed that number kills the product. The product is fine. It made $4,000 last month.

The reason it goes so wrong is that the fixed costs get counted twice. They are inside the $195, taking $75 off every unit's margin, and they are still sitting in the $60,000 numerator waiting to be covered. One pile of money, subtracted on both sides of the same division.

There is a second problem underneath, and it is the one that makes the mistake impossible to patch. The loaded cost depends on the volume you assumed to compute it, and volume is what you were trying to find. Watch the answer move.

Volume assumed	Fixed per unit	Loaded cost	"Break-even" it produces
400	150.00	270.00	Impossible, cost exceeds the $200 price
800	75.00	195.00	12,000 units
1,200	50.00	170.00	2,000 units

Three different break-evens for one product, decided entirely by a number you had to guess first. The correct calculation gives 750 at every volume, because contribution margin does not depend on how many units there are. That is not a convenience. It is the reason the split exists.

Picture your own company's cost-per-unit figure, the one in the pricing spreadsheet or the ERP. Does it have overhead baked into it? Most of them do, because that number was built for valuing inventory, not for deciding what one more sale is worth. Using it for the second job is the most common version of this mistake.

5. The query, and why break-even rounds up

Fixed costs $60,000, contribution $79 per unit. Divide them. What do you do with the 0.49 of a unit at the end?

Round up, always. You cannot sell half a grinder, and a fraction of a unit short of break-even is still short. 60,000 divided by 79 is 759.4937, and 759 units is not break-even. It generates 759 times 79, which is $59,961 of contribution, against $60,000 of fixed cost. The month loses $39. It takes 760 units, which throw off $60,040 and clear the base by $40.

SQL will not do that for you. Whole number divided by whole number truncates, so 60000 / 79 returns 759, which is the wrong direction. CAST(... AS INT) also truncates. The function that rounds up is CEIL, spelled CEILING in SQL Server, and some older SQLite builds ship without it.

Here is the query, with a ceiling built from the remainder rather than from CEIL.

SELECT product,
       price,
       variable_cost,
       price - variable_cost                             AS cm_per_unit,
       ROUND(100.0 * (price - variable_cost) / price, 1) AS cm_ratio_pct,
       fixed_costs / (price - variable_cost)
         + CASE WHEN fixed_costs % (price - variable_cost) > 0
                THEN 1 ELSE 0 END                        AS breakeven_units
FROM product_economics;

The last expression is worth reading slowly, because it is a small trick that saves an argument later. fixed_costs / (price - variable_cost) is the truncated whole number, 759. fixed_costs % (price - variable_cost) is the remainder left over, and the CASE adds 1 when there is a remainder and 0 when there is not. Add them and you have rounded up, with no CEIL and no floating-point arithmetic anywhere. On a division that comes out exact, like 60,000 over 80, the remainder is 0 and the answer stays 750.

You will also see this written as fixed_costs / cm + (fixed_costs % cm > 0), leaning on the comparison itself being 1 or 0. That is shorter and it is not portable. It runs in SQLite and MySQL, where a comparison is a number. PostgreSQL has a real true-or-false type that will not add to an integer, and SQL Server has no such value to add at all. Oracle spells the remainder MOD(fixed_costs, cm) rather than %. The CASE above is the version that survives the move, which is why it is the one printed here.

The 100.0 * in the ratio line is the other arithmetic guard. Without the decimal point, 100 * 80 / 200 is fine but 80 / 200 * 100 is 0, because the division happens first and 80 divided by 200 truncates to zero. The same trap runs through every ratio in SQL and is worked at length in gross vs operating vs net margin.

6. What a 10% discount actually costs

You cut the price 10%, from $200 to $180. By what percent does the contribution fall?

Twenty-five percent. The discount comes entirely out of the contribution, because the variable cost does not care what you charged. Contribution goes from $80 to $60, and $20 off $80 is a quarter of it.

This is the most useful thing on the page for anyone who sits near a sales team. A discount is not a small percentage off the price. It is a large percentage off the only part of the price that was yours. The lower the contribution ratio, the more brutal it gets. At a 40% contribution ratio, a 10% discount takes 25% of the contribution. At a 20% ratio it would take half.

What that does downstream, on the same product.

Measure	At $200	At $180	Change
Contribution per unit	80	60	-25%
Contribution ratio	40.0%	33.3%	-6.7 points
Break-even units	750	1,000	+33%
Units to hold the same $4,000 profit	800	1,067	+33%

Read the last row again, because it is the sentence to take into the meeting: a 10% discount needs 33% more units just to make the same money. Check it if you like. 1,067 units at $60 of contribution is $64,020, minus $60,000 of fixed cost, which is $4,020, near enough the $4,000 the product made before.

That is the number that should sit next to any discount proposal. Not "we lose 10% of revenue," which is what a price cut looks like on a revenue line, but "we need a third more volume to stand still," which is what it looks like in the only unit that matters. If the sales team is confident of a third more volume, the discount is a good idea. Often the discussion has never been framed that way, and this table is the whole contribution of the analysis.

7. The full before and after

Same product, same month, two ways of reporting it.

Before

"The Pro Grinder made $4,000 last month on 800 units. Unit cost is $195 against a $200 price, so we are making $5 a unit. Margin is too thin; recommend a price rise or a product review." Every number in that paragraph is arithmetically correct. The recommendation is built on the fully loaded $195, so it double-counts the fixed costs, and it puts a product with a 40% contribution ratio on a kill list.

After

Measure	Value	Read
Price	200
Variable cost	120	Parts, packaging, shipping, processing
Contribution per unit	80	What one more sale is worth
Contribution ratio	40.0%	Of every sales dollar
Fixed costs	60,000	Per month, unchanged by volume
Break-even	750 units, 150,000 revenue	Where the month turns positive
Current volume	800 units	Profit 4,000
Margin of safety	50 units, 6.25%	How far sales can fall
Operating leverage	16.0	1% on volume moves profit 16%

The same month, and now a completely different recommendation. The product is not thin on margin, it is thin on volume, and those need opposite responses. Its contribution ratio of 40% is healthy. It is sitting 50 units above break-even, and its leverage of 16 means an extra 50 units would double the profit while 50 fewer would erase it. The finding: the Pro Grinder is 6.25% of volume away from losing money and 40 cents of every dollar goes to profit once it clears, so this is a volume problem, not a pricing one.

8. Edge cases, including the order that looks like a loss

A customer offers to buy 150 grinders at $140 each, well under the $195 it "costs" to make one. Take it or leave it?

Take it, on these numbers. The variable cost is $120, so each of those units contributes $20. On 150 units that is $3,000 of extra contribution, and because the fixed costs are already covered by the regular 800 units, all $3,000 is profit. The month goes from $4,000 to $7,000. The floor on any special order is the variable cost, $120, and everything above it is worth having. This is the single most valuable use of contribution margin, and it is unavailable to anyone working from a loaded unit cost.

The conditions on that answer are real and they matter. It holds only if the discounted order does not eat capacity you would have sold at full price, and only if the low price does not leak into your regular customers' expectations. Both of those are judgement calls rather than arithmetic, and both belong in the same email as the $3,000.

Break-even is a monthly question or a yearly one, never a floating one. $60,000 of fixed cost is per month here, so 750 units is a monthly break-even. Mixing an annual fixed cost with a monthly volume is a twelvefold error and it is easy to make when the two numbers come from different systems. Put the period in the column header.

Multiple products need a weighted contribution. Break-even for a range of products depends on the mix, because a unit is not a unit any more. Use the blended contribution ratio and the revenue version of the formula, and state the mix you assumed, because the answer changes when the mix does.

Contribution margin is not gross margin. They look similar and they are cut differently. Gross margin subtracts cost of goods sold, which usually contains some fixed manufacturing overhead. Contribution margin subtracts only what varies with the unit. Two numbers, two purposes, and using one where the other belongs is exactly the mistake in section four wearing better clothes.

A negative contribution margin is a different conversation entirely. If the price is below the variable cost, every sale makes the loss bigger, and there is no volume that fixes it. Break-even does not exist, the formula returns a negative or an error, and the answer is to change the price or stop selling it. Guard the division so the report says that in words rather than printing a negative unit count.

Why this works

The split between fixed and variable is not just an accounting convention, it is a decision rule with a formal basis. Charnes, Cooper and Ijiri set out break-even and cost-volume-profit analysis as a constrained optimisation problem and showed the conditions the linear version depends on (Charnes, Cooper, & Ijiri, 1963, Journal of Accounting Research , 1(1), 16–43). Their conditions are the relevant range from section one, stated precisely: the arithmetic holds while the cost behaviour holds, and not outside it.

The reason the loaded unit cost in section four is so persistent has been measured too. Cooper and Kaplan documented how absorption systems, built to value inventory for external reporting, get reused for pricing and product decisions they were never designed for, and produce exactly the distortion on this page (Cooper & Kaplan, 1988, Accounting Horizons , 2(3), 61–66). The number is not wrong. It is answering a different question, and nothing on the report says so.

The question at the top of each section is deliberate. Attempting an answer before receiving one improves learning of that specific material across sixty-four studies (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Working out 60,000 divided by 80 yourself, before section three gave you 750, is why the formula will come back when you need it. The same evidence says the cheat sheet below works best covered and recalled rather than reread (Roediger & Karpicke, 2006, Psychological Science , 17(3), 249–255).

Using this on your own numbers

Re-sorting every cost in the business into two piles is a project, and it is not one anyone will fund. Do this instead, in order.

Pick one product and sort only its costs. Two columns, fixed and variable, and the one-more-unit test from section one to decide each line. An hour on one product beats a quarter on all of them.
Check whether your existing unit cost has overhead in it. Ask whoever owns it. If the answer is yes, or nobody knows, you cannot use it for contribution margin and section four is why.
Compute contribution per unit and the ratio. Two numbers, and they are the two that answer every question in this article.
Put break-even and the margin of safety on the product's regular report. Break-even alone is a fact. Break-even next to current volume is a warning system.
Round break-even up, and check it. Multiply your answer by the contribution and confirm it clears the fixed costs. Section five has the ceiling to use when your database has no CEIL.
Build the discount table before the next pricing conversation. Contribution, break-even, and units needed to hold profit, at the current price and the proposed one. It is four numbers and it changes how the meeting goes.

If there is paper nearby, draw this once and it stays. Draw the two bars from the top of this page from memory: a price bar split into variable and contribution with the fixed box standing separately beside it, then the same bar with the fixed share pushed inside and the contribution reduced to a sliver. Mark which one is right. If you draw the fixed box touching the bar, the page is still open.

This page is one of five. The Financial analysis set on the guides index is this page plus Gross vs Operating vs Net Margin, Current Ratio vs Quick Ratio, Net Present Value (NPV) and Budget vs Actual Variance. They share a worked company, so the numbers carry across. The index also holds every other how-to: SQL, Excel, Tableau, and the working habits around them.

The whole thing on one screen

Cover the right column, then work down. Answer before you look, especially where you are sure.

Concept	What it is
The sorting test	Sell one more unit. If the cost moves, it is variable.
Relevant range	Fixed only holds across the volumes you are actually considering.
Contribution margin	Price minus variable cost. 200 − 120 = $80 per unit.
Contribution ratio	Contribution ÷ price. 80 ÷ 200 = 40%.
Not profit per unit	There is no profit per unit until you know the count.
Break-even units	Fixed ÷ contribution per unit. 60,000 ÷ 80 = 750.
Break-even revenue	Fixed ÷ contribution ratio. 60,000 ÷ 0.40 = $150,000.
Margin of safety	Current volume minus break-even. 800 − 750 = 50 units, 6.25%.
Operating leverage	Contribution ÷ profit. 64,000 ÷ 4,000 = 16. Cuts both ways.
The loaded-cost error	Putting fixed inside the unit gave 12,000 units instead of 750.
Why it is 16x wrong	The fixed costs are counted twice, once in the unit and once in the total.
Why it cannot be patched	The loaded cost depends on the volume you were trying to find.
Rounding	Break-even rounds up. 759 units leaves the month $39 short.
The ceiling with no CEIL	`fixed/cm + CASE WHEN fixed % cm > 0 THEN 1 ELSE 0 END`. The shorter version without the CASE only runs in SQLite and MySQL.
The discount rule	10% off the price took 25% off the contribution.
What a discount needs	33% more units to make the same $4,000.
The special order	$140 beats the $120 variable cost, so 150 units added $3,000 of profit.
The price floor	Variable cost. Below it, volume makes the loss bigger.

The one habit to keep. The habit worth keeping. Never subtract a fixed cost from a price. Fixed costs go in the denominator of break-even, never inside the unit, and every version of this mistake starts with someone dividing overhead by a volume. If a spreadsheet fights back in a way this page does not cover, there is a general diagnosis loop for being stuck.

Last thing, and this one I am genuinely asking. The first product I ever recommended killing had a fully loaded cost a dollar under its price, and it was throwing off a 40-something percent contribution the whole time. What is a decision you have watched get made on a number that was answering a different question?

References

Charnes, A., Cooper, W. W., & Ijiri, Y. (1963). Breakeven budgeting and programming to goals. Journal of Accounting Research , 1(1), 16–43.
Cooper, R., & Kaplan, R. S. (1988). Measure costs right: Make the right decisions. Accounting Horizons , 2(3), 61–66.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science , 17(3), 249–255.

Originally published on Analyst Prep Kit: Contribution Margin and Break-Even: The Fixed Cost That Does Not Belong in the Unit

If it was useful: Buy Me a Coffee.

SUM vs SUMX in DAX: Why a Row-by-Row Calculation Needs an Iterator

Michael Nocito — Sun, 06 Sep 2026 13:00:23 +0000

By Michael Nocito, data analyst · Published August 10, 2026

By the end of this page you can look at any DAX aggregation and say which of the two it needs, before you write it. You will also be able to spot the wrong one in somebody else's model, including the version that returns a number close enough to pass. It takes about twenty minutes. Every figure below is printed next to the arithmetic that produced it, so you can check any of them on paper.

Here is the move. If the number you want to add up already sits in a column, use SUM(Orders[Units]). If it has to be worked out on each row first, use SUMX(Orders, Orders[Units] * Orders[UnitPrice]): a table, then the calculation to run on every row of it.

The short version: SUM adds a column that already exists. SUMX builds a value on each row, then adds those up.

The mistake that follows from missing this is worth seeing as a picture, because the arithmetic explains itself once you have.

The original carries a diagram here. In words: Two square grids sit side by side, each one sixteen cells wide and sixteen cells tall, one cell for every possible pairing of an order's quantity with an order's price. The left grid, labeled SUM times SUM, is completely filled in: all two hundred and fifty six cells are solid, because multiplying one total by another total combines every quantity with every price, including the pairs that came off different order lines and never occurred together. The right grid, labeled SUMX, is almost entirely empty outlines, with a single unbroken line of sixteen solid cells running corner to corner from top left to bottom right. Those sixteen are the pairings where the quantity and the price came off the same order line. The left grid is a solid block; the right grid is a thin diagonal stripe on an empty field.

Every number on this page is checkable. It runs on the sixteen-order fixture used across these Power BI guides, and every result is printed beside its arithmetic in the form 4 × 220 = 880. Every DAX behavior described is quoted from Microsoft's own reference pages in the "why this works" section rather than inferred. If measures against calculated columns is still fuzzy, columns vs measures comes first.

Orders , the fact table. Sixteen rows. The unit price is stamped on the line, which is what an order table does so that a later price change does not rewrite last year's sales.

OrderID	Region	Product	Units	UnitPrice
1001	North	Desk	4	220
1002	South	Chair	10	85
1003	East	Desk	3	220
1004	North	Lamp	6	40
1005	South	Desk	3	220
1006	East	Chair	8	85
1007	North	Chair	5	85
1008	West	Lamp	12	40
1009	South	Lamp	7	40
1010	East	Desk	5	220
1011	North	Desk	2	220
1012	West	Chair	9	85
1013	East	Lamp	15	40
1014	North	Chair	6	85
1015	South	Desk	4	220
1016	West	Desk	2	220

Products , the dimension table, joined one-to-many to Orders on Product.

Category	Product	ListPrice	Cost
Furniture	Desk	220	140
Furniture	Chair	85	52
Lighting	Lamp	40	22

There are 101 units in total, and no revenue column anywhere. Revenue is the number you have to build.

1. The fork: is the number already in a column?

Before the explanation, one question worth answering from memory. Think of the last measure you wrote. Say whether the values it added up were already stored somewhere, or whether the model had to work each one out first.

That question decides which function you reach for, and it has two answers.

Answer one: the number is already in a column. Units, Revenue, Amount, Headcount. Somebody stored it, one value per row, and adding it up is the whole job. If that is true you want SUM, and you hand it the column.

Answer two: the number does not exist yet. Line revenue when the table only has units and a price. Margin when the cost lives on another table. Weighted score, extended amount, anything with an operator in the middle of it. If that is true, something has to visit each row, work the value out there, and only then add them up. That something is SUMX.

What decides between them is not how complicated the formula looks. It is whether you could point at a column in the Fields pane and say "that one". If you can, it is SUM. If you have to describe a calculation instead, it is SUMX.

It matters because picking the wrong one does not always produce an error. Sometimes it produces a number, and the next three sections are about what that number is and why it survives.

2. What SUM will and will not accept

Microsoft's reference page for SUM gives it one parameter, and describes it in six words: "The column that contains the numbers to sum." Not an expression. A column.

So this does not work, and it is the single most common thing a new Power BI user types:

Revenue = SUM ( Orders[Units] * Orders[UnitPrice] )

Power BI refuses it with a message that is unusually direct for a formula engine: The SUM function only accepts a column reference as an argument. Nothing is stored in the model called Units * UnitPrice, so there is no column to hand over.

That refusal is the helpful outcome. It stops you at the point of the mistake. The damaging version is the one that compiles, which is what happens when somebody works around the error by summing each column on its own:

Revenue = SUM ( Orders[Units] ) * SUM ( Orders[UnitPrice] )

Two columns, two legal SUMs, one multiplication. Power BI accepts it without complaint, because there is nothing wrong with it as DAX. It is only wrong as arithmetic.

3. The failure: 2,630 that reads 12,995

Take the first four orders and do it by hand. Four rows is small enough to check every step, and the shape of the error is identical at sixteen rows or sixteen million.

OrderID	Units	UnitPrice	Line revenue
1001	4	220	880 (4 × 220)
1002	10	85	850 (10 × 85)
1003	3	220	660 (3 × 220)
1004	6	40	240 (6 × 40)

The right answer is 2,630 , because 880 + 850 + 660 + 240 = 2,630. That is what SUMX returns: it works out each line, then adds the four results.

Now the version that compiles. SUM(Units) is 4 + 10 + 3 + 6 = 23. SUM(UnitPrice) is 220 + 85 + 220 + 40 = 565. Multiply them and you get 12,995 , because 23 × 565 = 12,995.

Before reading on, say why 12,995 is so much bigger than 2,630. It is not rounding and it is not a doubled join. Say what those extra 10,365 are made of.

They are pairings that never happened. Multiplying two totals is the same as adding up every quantity crossed with every price: order 1001's four units at order 1002's price of 85, order 1004's six units at the desk price of 220, and so on through all sixteen combinations. Four rows produce 4 × 4 = 16 pairings. Only four of them are real, the ones where the quantity and the price came off the same line, and those four are the 2,630.

The gap is the other twelve: 12,995 minus 2,630 = 10,365.

Run the same thing on the full sixteen orders and the numbers get louder. SUMX returns 9,890. SUM(Units) is 101, SUM(UnitPrice) is 2,125, and 101 × 2,125 = 214,625. Sixteen rows means 16 × 16 = 256 pairings, and 240 of them are fiction.

Here is the working measure.

Total Revenue =
SUMX ( Orders, Orders[Units] * Orders[UnitPrice] )

Read it as two arguments doing two jobs. Orders is the table to walk. Orders[Units] * Orders[UnitPrice] is the calculation to run while standing on each row of it. SUMX adds the sixteen results.

4. Why nobody catches it: slice to one row and both agree

Before the explanation, a prediction. You have a table visual showing revenue, and you drill all the way down to a single order to check the measure. Say whether the broken version and the working version show the same figure in that cell.

They do, exactly, and this is the part worth carrying away from the page.

Filter the visual to order 1001 alone. SUMX walks one row and returns 4 × 220 = 880. The broken measure computes SUM(Units) over that one row, which is 4, and SUM(UnitPrice) over that one row, which is 220, then multiplies: 4 × 220 = 880. Identical.

That is not a coincidence about this fixture. With one row there is only one pairing available, so "every pairing" and "the real pairing" are the same set. The two measures cannot disagree.

What the visual is sliced to	Broken measure	Working measure
Order 1001 alone	880	880
Order 1013 alone	600 (15 × 40)	600 (15 × 40)
Lamps, four orders	6,400 (40 × 160)	1,600
All sixteen orders	214,625	9,890

The lamp row needs its own line, because it is the one that shows where the agreement stops. Lamps are four orders of 6, 12, 7 and 15 units, all at 40. SUMX gives 240 + 480 + 280 + 600 = 1,600. The broken measure gives SUM(Units) = 40 and SUM(UnitPrice) = 160, and 40 × 160 = 6,400. Four times too big, because four rows means sixteen pairings and only four are real.

So the test that proves the measure works is the test that cannot fail. A single-row check passes on a measure that is wrong by a factor of sixteen at the total. This is the same shape as the Excel fill whose first row is right in both the working and the broken version, and it is why the habit at the end of this page is about totals rather than about being careful.

5. What SUMX is actually doing on each row

Microsoft calls SUMX an iterator function , and defines the category in one sentence: "A DAX function that enumerates all rows of a given table and evaluate a given expression for each row."

The word that matters underneath that is row context. Row context is the model knowing which single row it is standing on, which is what makes Orders[Units] mean a number rather than a whole column. Microsoft's glossary puts it plainly: row context "represents the 'current row', and is used to evaluate calculated column formulas and expressions used by table iterators."

So the sequence inside SUMX(Orders, Orders[Units] * Orders[UnitPrice]) is:

Take the rows of Orders that survived the filters on the visual.
Stand on the first one. Orders[Units] is 4 and Orders[UnitPrice] is 220, so the expression is 880.
Move to the next. Repeat. Sixteen times.
Add up the sixteen results and return one number.

Nothing is stored. There is no revenue column at the end of it, which is the difference between this and a calculated column. A calculated column would compute 880 once, save it in the file, and use memory forever. SUMX computes it when a visual asks and throws it away.

One thing to know rather than discover: a row context does not filter anything. Standing on row 1001 does not restrict other tables to that order. Referencing a measure inside the iterator changes that, and it changes it silently, which is the trapdoor covered in CALCULATE and filter context. Read that one before you write SUMX(Orders, [Some Measure]).

Now picture this running on a table you actually own. Take your own fact table, find one number on a report that had to be worked out rather than looked up, and say out loud which table SUMX would walk and what the expression on each row would be. Two pieces, that is the whole call.

6. The rest of the X family

SUMX is not special. Every aggregation in DAX has an X twin, and they all take the same two arguments in the same order: a table, then an expression to evaluate on each row of it. Learning SUMX teaches you all of them at once.

Plain version	Iterator	Reach for the iterator when
`SUM(col)`	`SUMX(table, expr)`	The amount has to be calculated per row first.
`AVERAGE(col)`	`AVERAGEX(table, expr)`	You want the mean of a per-row calculation.
`MAX(col)`	`MAXX(table, expr)`	The biggest calculated value, not the biggest stored one.
`MIN(col)`	`MINX(table, expr)`	Same, in the other direction.
`COUNT(col)`	`COUNTX(table, expr)`	Counting rows where an expression produces a number.
none	`RANKX(table, expr)`	Ranking on something the model works out per row.
none	`CONCATENATEX(table, expr, delim)`	Turning rows into one readable string for a card.

CONCATENATEX is the one worth trying first, because it makes the row-by-row idea visible. It takes an optional third argument, the separator, and it is how a report shows "Desk, Chair, Lamp" in a title instead of a number.

AVERAGEX has one behavior worth knowing before it surprises you in a matrix. Microsoft states it directly: "When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0." A blank cell and a zero cell mean different things, and only one of them draws a bar.

7. The wrong number that looks right: an average of percentages

Before this one: a report needs the overall margin percentage. Somebody writes a margin percent for each order and averages it. Say whether that gives the same answer as total margin divided by total revenue.

It does not, and unlike the revenue example the gap here is small enough to ship.

Cost lives on Products, not on Orders, so margin is a number that exists nowhere and has to be built. RELATED is what lets a row of Orders read its own product's row on the other side of the relationship.

Total Margin =
SUMX (
    Orders,
    Orders[Units] * ( Orders[UnitPrice] - RELATED ( Products[Cost] ) )
)

Per unit that is 220 - 140 = 80 on a desk, 85 - 52 = 33 on a chair, and 40 - 22 = 18 on a lamp. Across the sixteen orders there are 23 desk units, 38 chair units and 40 lamp units, so total margin is 23 × 80 + 38 × 33 + 40 × 18 = 1,840 + 1,254 + 720 = 3,814.

Revenue is 9,890. So the blended margin is 3,814 / 9,890 = 38.56%.

Now the version that averages. Every desk order runs at 80 / 220 = 36.36%, every chair at 33 / 85 = 38.82%, every lamp at 18 / 40 = 45.00%, whatever the quantity. There are seven desk orders, five chair orders and four lamp orders, so:

Average Margin % =
AVERAGEX ( Orders, DIVIDE ( [Order Margin], [Order Revenue] ) )

Product	Margin %	Orders	Contribution to the average
Desk	36.36% (80 / 220)	7	7 × 36.36 = 254.55
Chair	38.82% (33 / 85)	5	5 × 38.82 = 194.12
Lamp	45.00% (18 / 40)	4	4 × 45.00 = 180.00
Total		16	628.66 / 16 = 39.29%

39.29% against a true 38.56%. Wrong by 0.73 percentage points, and entirely believable in a card on a dashboard.

The reason is worth saying in one line, because it is the whole class of error. The average gives every order one vote. Lamps are 4 of the 16 orders, so they carry 25% of the vote, but they are only 1,600 of the 9,890 in revenue, which is 16.18%. Lamps also have the highest margin percentage, so overweighting them pulls the average up.

A ratio of totals is not the total of ratios. The fix is to divide once, at the end:

Margin % = DIVIDE ( [Total Margin], [Total Revenue] )

The rule that comes out of this: SUMX the numerator, SUMX the denominator, and divide those two. Never average a percentage that was already a ratio, unless somebody has told you in writing that they want each order to count equally.

8. When SUM is the correct answer

Nothing above is an argument for using iterators everywhere. Microsoft's own guidance, on the SUMX page itself, is the opposite: "If you do not need to filter the column, use the SUM function."

SUM is right whenever the value is already sitting in the column, which covers most of a normal model. SUM(Orders[Units]) is 101 and there is nothing an iterator would add to that. A fact table that stores an extended amount per line should be summed, not re-derived.

There is a second case worth naming, because it looks like laziness and is not. If your fact table already has a stored revenue column, using it is more accurate than recomputing units times price, because the stored value is what was actually invoiced. Rounding, discounts and price overrides all live in that stored number and none of them are in the multiplication.

And if you find yourself writing SUMX(Orders, Orders[Units]), with a bare column as the expression and no calculation in it, that is a SUM with extra syntax. Write the SUM.

The full before and after

Same model, same visual, same sixteen rows. One function apart.

	Before: `SUM(Units) * SUM(UnitPrice)`	After: `SUMX(Orders, Units * UnitPrice)`
Does it compile	Yes	Yes
One order (1001)	880	880
Lamps, four orders	6,400 (40 × 160)	1,600
Desks, seven orders	35,420 (23 × 1,540)	5,060 (23 × 220)
All sixteen orders	214,625 (101 × 2,125)	9,890
What it is adding	All 256 quantity-and-price pairings	The 16 pairings that happened
What a reviewer checks first	A single row, which matches	A single row, which matches

The last line is the point of the page. Both versions pass the check most people run.

Edge cases that return a number instead of an error

These are the ones that produce output rather than a red squiggle.

Averaging a percentage. Section 7 in one line: AVERAGEX over a ratio weights every row equally, so the answer drifts by however unbalanced your rows are. Divide two totals instead.
Blanks are not zeros. The SUMX reference says only numbers are counted and "blanks, logical values, and text are ignored". A row with a blank price contributes nothing rather than erroring, so a partly-loaded column produces a total that is quietly low.
A measure inside the iterator. SUMX(Products, [Total Revenue]) does not do what the syntax suggests, because referencing a measure in a row context triggers context transition automatically. That is a whole mechanism, and it is worked through on the CALCULATE page.
Iterating the wrong table. SUMX(Products, ...) walks three rows and SUMX(Orders, ...) walks sixteen. Both compile. The first argument is a decision, not boilerplate, and if it is a dimension table you are almost always on the wrong one. There is more on which table is which in star schema in Power BI.
Nesting an iterator inside an iterator. Legal, and it multiplies the work: a SUMX over 16 rows containing a SUMX over 16 rows evaluates 256 times. Fine here, and not fine on a fact table with ten million rows.
Using it where a calculated column belongs. If the per-row value is needed as a slicer or an axis, a measure cannot give you that. That decision has its own page: calculated column vs measure.

Why this works

The design reason is the useful part. DAX separates the two functions because they answer different questions, and Microsoft's reference pages say so in their opening lines. SUM "adds all the numbers in a column". SUMX "returns the sum of an expression evaluated for each row in a table". One reads storage, the other runs a calculation.

The failure is worth taking seriously because it is a specific case of something general: a calculation done on totals is not the same calculation done on the individual rows and then totalled. That is not a Power BI quirk. W. S. Robinson demonstrated the same thing for correlations in 1950, showing that relationships computed from grouped census data did not match the relationships among the individuals inside those groups, which is the finding that became known as the ecological fallacy (Robinson, 1950, American Sociological Review , 15(3), 351–357). The arithmetic here is simpler than his, but the mistake is the same one: aggregate first and the row-level relationship is gone before you use it.

The reason it survives a code review is arithmetic rather than psychology. Every check on a single row returns identical results from both measures, as shown in section 4. So the reviewer's evidence is real, and it is evidence about nothing. The only test that separates the two measures is one that spans more than one row, which means a total you predicted before you looked at it.

One note on the cheat sheet below. It is built to be covered and recalled rather than read, because testing yourself on material transfers to new situations better than restudying it, which matters here since you will be applying this to models that look nothing like the one above (Butler, 2010, Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133).

Using this on your own model

Auditing every measure in an inherited file is miserable and you will stop on the second table. Do this instead, in order.

Search the measure list for) * SUM and ) / SUM. Two aggregations combined with an operator between them is the pattern. Most of the hits will be fine. The ones that are not are all here.
For each hit, ask whether the two columns come from the same row. Units and price on the same line are a row-level pair, and multiplying their totals is wrong. Revenue and headcount from different tables usually are not, and dividing their totals is right.
Check a total, never a row. Take the filter off, read the grand total, and compare it to something you know. A revenue figure twenty times your annual sales fails in one second.
Check a group with more than one row in it. If a single-row slice is all you tested, you tested nothing. Pick the product with the most orders behind it.
Find every averaged percentage. Anything named "Average X %" or "Avg Margin" is worth opening. If it averages a ratio, replace it with DIVIDE of two totals and tell whoever owns the report that the number will move.
Write the intended arithmetic in the measure description. "Sum of units times unit price, per line" is one sentence, it sits in the model, and it makes the next person's review possible.

If you have paper nearby, one optional sketch fixes this permanently. Draw a four-by-four grid of boxes. Write four quantities down the side and four prices across the top. Shade the four boxes where a quantity meets its own price. What you have shaded is SUMX, and the whole grid is what multiplying two totals gives you. You will not need to look this up again.

More detail on this, and more like it. Every how-to sits in one place on the guides index: Power BI, SQL, Excel, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Thing	What it does
`SUM(col)`	Adds a column that already exists. One argument, and it must be a column.
`SUMX(table, expr)`	Evaluates the expression on every row, then adds the results.
The question to ask	Can I point at a column in the Fields pane? If not, it is SUMX.
Iterator	A function that walks every row of a table and evaluates an expression there.
Row context	The model knowing which single row it is standing on.
Row context does not	Filter anything. Referencing a measure is what changes that.
`SUM(a) * SUM(b)`	Adds every pairing of a with b. 16 rows means 256 pairings.
Why it passes review	Sliced to one row, both measures return the same number.
The test that works	A total you predicted before you read it.
The X family	AVERAGEX, MAXX, MINX, COUNTX, RANKX, CONCATENATEX. Same two arguments.
Average of a ratio	Weights every row equally. 39.29% where the truth is 38.56%.
Fix for a ratio	`DIVIDE([Total Margin], [Total Revenue])`. Divide once, at the end.
Reading another table	`RELATED`, from the many side to the one side.
Blanks in SUMX	Ignored, not zero. A half-loaded column totals quietly low.
`SUMX(t, t[col])`	A SUM with extra syntax. Write the SUM.
When SUM is right	The value is stored. Microsoft: "If you do not need to filter the column, use the SUM function."

The one habit to keep. Predict the grand total before you read it. Sixteen orders at these prices is roughly ten thousand, so 214,625 is wrong before you have opened a single measure. A rough number you committed to in advance catches this class of mistake faster than reading DAX will, and it costs about four seconds.

One last thought, and I would genuinely like other people's answers. The worst version I have seen was an average discount percentage on an executive card. It was built as an average of per-order discounts, it ran about two points under the real blended figure, and it had been quoted in board papers for three quarters before anyone divided the two totals. What is the longest one of these has gone unnoticed in a model you inherited?

References

Robinson, W. S. (1950). Ecological correlations and the behavior of individuals. American Sociological Review , 15(3), 351–357.
Butler, A. C. (2010). Repeated testing produces superior transfer of learning relative to repeated studying. Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133.
Microsoft. SUM function (DAX), SUMX function (DAX), AVERAGEX function (DAX), CONCATENATEX function (DAX) and DAX glossary. Microsoft Learn. All quotations above are taken from these reference pages.

Originally published on Analyst Prep Kit: SUM vs SUMX in DAX: Why a Row-by-Row Calculation Needs an Iterator

If it was useful: Buy Me a Coffee.

Row-Level Security in Power BI: Who Gets Filtered and Who Does Not

Michael Nocito — Sun, 06 Sep 2026 13:00:15 +0000

By Michael Nocito, data analyst · Published August 10, 2026

By the end of this page you can set up row-level security, and, more usefully, predict what any given person will actually see before they open the report. You will know the one setting that switches the whole thing off without any warning, why adding somebody to a second role gives them more data rather than less, and what the built-in test proves and what it quietly does not. It takes about twenty-five minutes. Every number below is printed next to the arithmetic that produced it.

Here is the move. You write a rule in Power BI Desktop under Modeling › Manage roles , something like [Region] = "East". You publish. Then you go to the Power BI Service, open the semantic model's Security page, and add people to that role. Two tools, two steps, and skipping the second one is the most common reason RLS appears to do nothing.

The short version: a role is a filter that answers TRUE or FALSE for every row, and rows that answer FALSE are removed. Whether it binds a particular person depends on their workspace role, not on the filter.

That second sentence is where almost all the damage lives, so it gets the picture.

The original carries a diagram here. In words: On the left is a tall stack of sixteen identical narrow bars, drawn in outline, standing for every row in the model. Two routes lead away from that stack to the right. The upper route runs into a funnel, wide where it enters and narrow where it leaves, and comes out the far side as a short stack of only four solid bars, marked Viewer. The lower route leaves the same stack, curves underneath the funnel without touching it, and arrives on the right as a full stack of all sixteen solid bars, marked Admin, Member, Contributor. Same model, same rule, same starting rows. The only difference between the two routes is whether they meet the funnel at all, and the route that misses it delivers everything.

Every number on this page is checkable. It runs on the sixteen-order fixture used across these Power BI guides, and every total is printed beside its arithmetic. Every behavior described is quoted from Microsoft's own documentation in the "why this works" section rather than inferred, because this is the one topic on the site where being approximately right is the same as being wrong.

Orders , the fact table, with revenue already worked out per line. Sixteen rows, four regions.

OrderID	Region	Product	Units	Revenue
1001	North	Desk	4	880
1002	South	Chair	10	850
1003	East	Desk	3	660
1004	North	Lamp	6	240
1005	South	Desk	3	660
1006	East	Chair	8	680
1007	North	Chair	5	425
1008	West	Lamp	12	480
1009	South	Lamp	7	280
1010	East	Desk	5	1,100
1011	North	Desk	2	440
1012	West	Chair	9	765
1013	East	Lamp	15	600
1014	North	Chair	6	510
1015	South	Desk	4	880
1016	West	Desk	2	440

Four regional totals, and they are the four numbers the rest of the page checks against.

Region	Orders	Revenue	Arithmetic
North	5	2,495	880 + 240 + 425 + 440 + 510
South	4	2,670	850 + 660 + 280 + 880
East	4	3,040	660 + 680 + 1,100 + 600
West	3	1,685	480 + 765 + 440
All	16	9,890	2,495 + 2,670 + 3,040 + 1,685

1. The fork: can this person edit the model, or only read it?

Before the explanation, one question worth answering from memory. Think of the last Power BI report you shared with somebody. Say whether that person could open the underlying model and change a measure, or whether they could only look at what you built.

That question, not the rule you wrote, decides whether row-level security does anything at all. There are two answers.

Answer one: they can only read. Their workspace role is Viewer, or they got the report through a published app. If that is true, every rule you wrote runs against every query they make, and they see only the rows their role permits.

Answer two: they can edit. Their workspace role is Admin, Member or Contributor. If that is true, row-level security does not apply to them at all. Not partially, not with a warning. They see every row in the model, and the report gives them no sign that a rule exists.

What decides between them is a dropdown in the workspace access panel, which is usually set by whoever added the person, often months earlier, and usually not by whoever wrote the security rule. That gap between the two decisions is where this fails.

It matters because the failure is silent on both sides. The person seeing too much has no idea, and the person who built the model tested it and watched it work.

2. What a role actually is

A role is a name plus one or more filters, and each filter is attached to a table. The filter is a DAX expression that gets asked about every single row and has to answer TRUE or FALSE. Microsoft's wording is worth having exactly: "A DAX filter evaluates TRUE/FALSE for each row. Only rows that return TRUE are visible. Everything else is completely removed."

Completely removed is the part to hold on to. This is not a report filter that a user can clear, and it is not a default someone can override in the Filters pane. The rows are gone before the visual is drawn.

The simplest useful rule, typed into the DAX editor under Modeling › Manage roles, is a comparison:

[Region] = "East"

Say out loud what that returns for order 1004, which is a North lamp. FALSE, so the row is dropped. Order 1013 is an East lamp, so TRUE, so the row stays. Sixteen questions, four answers of TRUE, and the model an East role member queries has four rows in Orders and totals 3,040.

One naming rule that catches people, and it is documented rather than folklore: a role name cannot contain a comma. London,ParisRole is rejected.

Filters land on one table and then travel. RLS filters propagate through the model's relationships the same way any other filter does, which is why it is usually better to put the rule on a small dimension table and let it flow to the fact table. That is one more argument for a proper star schema, and Microsoft's guidance says so directly: "it's often more efficient to enforce RLS filters on dimension tables, and not fact tables."

3. Two tools, two steps: where roles live and where people do

This trips up more exam candidates than any other part of the topic, and the reason is that the job is split across two different products.

Step	Where	What you do
1. Define the role	Power BI Desktop	Modeling › Manage roles. Name it, pick a table, write the DAX filter.
2. Publish	Desktop to the Service	The role definitions travel with the model. The membership does not, because it does not exist yet.
3. Assign people	Power BI Service	Semantic model › Security. Add users or security groups to each role.
4. Validate	Either	View As in Desktop, or Test as role in the Service.

You cannot assign users to a role in Desktop. The option is not hidden or buried, it is not there, because Desktop has no idea who is in your organization. And the Security page in the Service only appears for models that already have roles defined, so a model with no roles gives you nothing to click and no explanation.

Two smaller facts that come up. Only the semantic model's owner or a workspace admin can add members to roles. And Microsoft 365 groups cannot be used for role membership at all; security groups, distribution groups and mail-enabled groups can.

Anyone who has published a model but not assigned anybody gets the outcome that looks like a bug and is not. A user in no role at all typically sees nothing rather than everything, because the rule is enforced and no role permits any row.

4. The failure: the East manager who sees 9,890

Here it is worked end to end, and it is the version that actually happens.

Priya runs the East region. You build the model, write [Region] = "East" into a role called East, publish it, add Priya to the role in the Service, and test it. The test passes: four orders, 3,040.

Some weeks earlier, though, Priya was added to the workspace so she could open the other three reports the team keeps there. She was given Member. Nobody connected the two decisions, because they were made by different people for different reasons on different days.

Priya opens her regional card. It reads 9,890.

What was intended	What she sees	Gap
3,040, the four East orders	9,890, all sixteen orders	6,850 (9,890 − 3,040)

Before reading on, say why the report gives her no clue. The rule is still there, the role is still assigned, and she is still a member of it.

Because there is nothing to show. RLS does not grey anything out or add a banner. It removes rows before the query returns, so a report with no rows removed is identical to a report that never had a rule. Priya's card is not displaying an error. It is displaying the correct total for the rows she was given, and she was given all of them.

The fix is one dropdown, and it is not in the model. In the workspace access panel, change Priya from Member to Viewer. She keeps the reports, loses nothing she uses, and the rule starts running. If she needs to build her own reports on top of the model, give her Viewer plus Build permission on the semantic model, because RLS still applies to Viewers who have Build. Microsoft states that case explicitly, including for Analyze in Excel.

Now picture this on a workspace you actually own. Open the access list and read the roles rather than the names. Say out loud how many people on it could edit, and whether every one of them is supposed to see every row. That count is the real scope of your security rule.

5. Two roles is a union, not an intersection

Before the explanation, a prediction. Somebody is in a role that permits East, and you also add them to a role that permits lamps. Say whether they end up seeing East lamps only, or something larger.

Something larger, and it is the answer most people get wrong because every other permission system they have used behaves the other way.

Microsoft's guidance is unambiguous: "When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters." And the sentence right after it is the one to memorize, because it names the assumption being broken: "unlike permissions applied to SQL Server database objects (and other permission models), the 'once denied always denied' principle doesn't apply."

Worked on the fixture. The East role permits orders 1003, 1006, 1010 and 1013. A Lamps role permits every lamp order, which is 1004, 1008, 1009 and 1013.

What you get	Orders	Revenue	Arithmetic
East role alone	1003, 1006, 1010, 1013	3,040	660 + 680 + 1,100 + 600
Lamps role alone	1004, 1008, 1009, 1013	1,600	240 + 480 + 280 + 600
What people expect from both	1013	600	The one East lamp
What both actually give	seven orders	4,040	660 + 240 + 680 + 480 + 280 + 1,100 + 600

Expected 600, delivered 4,040, and 4,040 minus 600 = 3,440 of rows nobody meant to hand over. Adding a second role widened the access, which is the opposite of what "adding a restriction" sounds like.

Microsoft's own illustration of this is starker than mine and worth keeping in your head as the extreme case. A Workers role restricts a Payroll table with the rule FALSE(), so it returns no rows at all. A Managers role uses TRUE(), so it returns everything. Somebody who maps to both roles sees the entire Payroll table. The deny does not win. There is no deny.

The fix is a design rule rather than a setting: one person, one role. If a person needs East and lamps together, build a single role that carries both filters, one on each table, so both conditions apply at once. Microsoft's guidance recommends exactly this, and the reason it gives is worth noting: a user can end up in a second role indirectly, through a security group, without anybody deciding to put them there.

6. Dynamic RLS, and the typo that opens the whole table

Writing one role per region does not scale past a handful. Dynamic RLS is one role for everybody, where the rule compares a column to whoever is signed in.

You need a small mapping table, one row per person, with their sign-in address and what they are allowed to see. Then the rule goes on that table:

[UserEmail] = USERPRINCIPALNAME()

USERPRINCIPALNAME takes no arguments and returns the user principal name at connection time, which looks like an email address. The filter lands on the mapping table, the mapping table is related to the region, and the filter travels down that relationship to Orders. One role, one rule, and adding a new manager becomes a row in a table rather than a change to the model.

There is an older function, USERNAME, and the difference is a real trap rather than trivia. In Power BI Desktop, USERNAME returns DOMAIN\User. In the Power BI Service, USERNAME and USERPRINCIPALNAME both return the user principal name. So a rule built and tested against a domain-style value in Desktop can behave differently once published. Use USERPRINCIPALNAME and store matching values in the mapping table.

Now the part worth the whole section. This pattern, which reads perfectly sensibly, hands over the entire table to a typo:

IF (
    USERNAME () = "Worker",
    [Type] = "Internal",
    TRUE ()
)

Workers see internal rows. Everybody else, meaning managers, sees everything. But "everybody else" is not a list of managers. It is every value that is not exactly the string Worker, so Wrker falls into the else branch and returns TRUE for every row. Microsoft's guidance uses this exact example, and its fix is to test for each expected value and make the fall-through deny:

IF (
    USERNAME () = "Worker",
    [Type] = "Internal",
    IF (
        USERNAME () = "Manager",
        TRUE (),
        FALSE ()
    )
)

The habit underneath it: in a security rule, the last branch is the one that runs when you have not thought of the case. Make it return nothing.

7. What View As proves, and what it does not

Desktop has View As on the Modeling ribbon, and the Service has Test as role on the model's Security page. Both are genuinely useful and both prove less than people think.

What they prove: your DAX filter is valid, it lands on the table you meant, and it propagates through the relationships you expect. That is most of the work, and testing every role this way is not optional.

What they do not prove, and this is documented rather than folklore:

They cannot tell you a person's workspace role. The whole failure in section 4 is invisible to both tools, because both simulate the rule and neither simulates the bypass.
For dynamic RLS, the test uses your own identity. USERPRINCIPALNAME returns your sign-in name, not the one you are trying to simulate. So a passing test tells you the rule works for you.
Not every surface is covered. Test as role does not work for paginated reports, and it does not validate Q&A visuals, Quick insights or Copilot. It also does not work for DirectQuery models with single sign-on.

The test that closes the gap is unglamorous and takes two minutes: put a card on the report showing USERPRINCIPALNAME(), publish, and have one real person in each audience open it and tell you what it says next to their totals. Microsoft's troubleshooting guidance suggests the same measure, named something like "Who Am I". A real user's session is the only thing that exercises both halves at once.

8. Four things RLS does not do

Each of these is somebody's disappointed afternoon.

It does not hide columns or measures. Microsoft is direct about it: RLS "filters table rows. They can't be configured to restrict access to model objects, including tables, columns, or measures." If a person can see a row, they can see every column on that row, salary included. The feature for hiding a column is object-level security, and it is separate.

It does not let people see a total without the detail. Asked whether RLS can hide detailed rows while still showing them in a summary, the answer is no: you secure rows, and users can see either the details or the summary of what they have. Getting a company-wide total next to a personal one needs a separate summary table built for that purpose, not a cleverer rule.

It cannot be overridden by DAX, in either direction. This one cuts both ways and is worth knowing. No measure can escape RLS, and Microsoft goes further: a DAX expression "can't even determine that RLS is enforced". So there is no writing a smarter measure to work around it, and equally no accidentally punching a hole in it with ALL or REMOVEFILTERS. If you want the mechanics of what a measure can and cannot see, that is CALCULATE and filter context.

It does not follow the model everywhere. Live connections to Analysis Services enforce security in that model rather than in Power BI, and the Security option does not even appear. Publish to web does not work with RLS at all. Service principals cannot be added to a role, so an app authenticating that way is not filtered by one.

The full before and after

Same model, same rule, same person. One dropdown apart.

	Before: Priya is a workspace Member	After: Priya is a Viewer
Role exists in the model	Yes	Yes
Priya is assigned to it	Yes	Yes
View As in Desktop	Passes, shows 3,040	Passes, shows 3,040
Test as role in the Service	Passes, shows 3,040	Passes, shows 3,040
What Priya actually sees	9,890, all sixteen orders	3,040, her four orders
Can she still open the reports	Yes	Yes
Can she still build her own	Yes	Yes, with Build permission, still filtered
Anything on screen that says so	No	No

The last two lines are the point of the page. Every test you can run from your own desk passes in both columns.

Edge cases that return the wrong rows quietly

These produce data rather than an error, which is the only kind that matters here.

An inactive relationship. RLS filters only travel along active relationships. A model with a second, inactive date relationship will not carry the security filter down it, so a visual built on that path can show rows the role never permitted.
A bi-directional relationship you assumed was enough. Security filtering runs one way by default, whether or not the relationship itself is bi-directional. Propagating security both ways is a separate checkbox on the relationship, "Apply security filter in both directions", and it is off until you tick it.
A user in a security group you forgot about. The second role does not have to be assigned deliberately. Group membership puts people into roles, roles are additive, and the resulting access is the union.
A changed name. Dynamic RLS matches a stored string against a live sign-in name. Somebody's account changes and their row stops matching, so they abruptly see nothing, and it looks like an outage rather than a mapping problem.
An import model whose source already has security. Importing does not bring the source's security rules with it: on import you must define RLS in Power BI. With DirectQuery the source's own rules apply, which is a genuinely different arrangement to reason about.
A calculated column doing the filtering job. Rules belong in the role. Building the same logic into a column and hoping it filters is a category error, and which of the two you should be writing is decided in calculated column vs measure.

Why this works

The design reason for the bypass is defensible once you say it plainly: people who can edit the model can already see the data, because editing a model means reading it. A Contributor can open the semantic model, write their own measure and read any number in it. Filtering their report while leaving the model open to them would be a lock on a door with no wall attached. Microsoft states the boundary in one line: "RLS only restricts data access for users with Viewer permissions. It doesn't apply to workspace Admin, Member, or Contributor roles."

So the control is real, and its scope is a workspace role. That makes this an instance of the oldest rule in the field. Saltzer and Schroeder set out eight design principles for protecting information, and the one this page keeps running into is least privilege: every user should operate with the smallest set of privileges the job needs (Saltzer & Schroeder, 1975, Proceedings of the IEEE , 63(9), 1278–1308). Priya was given edit rights so she could read four reports. The extra privilege was not the point of the decision and it silently outranked the security rule.

Their other principle worth naming here is fail-safe defaults: base access on permission rather than exclusion, so the default outcome is no access. That is exactly the shape of the additive-roles behavior. A rule returning FALSE grants nothing; it does not deny. Union it with a rule that grants everything and everything is what you get, which is why the fix is one role per person rather than a carefully layered stack of them.

One note on the cheat sheet below. It is built to be covered and recalled rather than read, because testing yourself on material transfers to new situations better than restudying it, which matters here since you will be applying this to workspaces that look nothing like the one above (Butler, 2010, Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133).

Using this on your own model

Auditing every role in an inherited tenant is miserable and you will stop at the second workspace. Do this instead, in order.

Open the workspace access list before you open the model. Read the roles, not the names. Everyone listed as Admin, Member or Contributor sees every row, whatever your rules say. That list is the answer to "who can see everything", and it takes thirty seconds.
Move every consumer to Viewer. If somebody needs to build on the model, Viewer plus Build permission does that and stays filtered. Editing rights are for people who edit.
Count roles per person, not people per role. Anybody in two roles is getting the union. Expand the security groups, because that is where the second role hides.
Read the last branch of every dynamic rule. If the fall-through is TRUE, an unexpected sign-in name returns the whole table. Make it FALSE and test with a value you know is wrong.
Publish a "Who am I" card. One card with USERPRINCIPALNAME() next to the totals, and one real person from each audience telling you what it says. This is the only test that covers the workspace role and the rule at the same time.
Write down who each role is for. One sentence per role, stored with the model. Roles outlive the person who wrote them, and a role nobody can explain is a role nobody will dare remove.

If you have paper nearby, one optional sketch makes the additive rule stick. Draw two overlapping circles, label one East and one Lamps, and shade the small piece where they overlap. That is what most people expect two roles to give. Now shade both whole circles. That is what two roles actually give. You will not get this one wrong again.

More detail on this, and more like it. Every how-to sits in one place on the guides index: Power BI, SQL, Excel, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Thing	What it does
What a role is	A DAX filter asked of every row. TRUE stays, FALSE is removed completely.
Where roles are defined	Power BI Desktop, Modeling › Manage roles.
Where members are assigned	The Power BI Service, semantic model › Security. Never in Desktop.
Who RLS applies to	Viewers, and app consumers. That is the whole list.
Who it does not apply to	Workspace Admin, Member and Contributor. By design, with no warning.
Viewer plus Build	Still filtered, including through Analyze in Excel.
Two roles	A union. More data, not less. "Once denied always denied" does not apply.
The design rule	One person, one role. Put both filters in the same role instead.
A user in no role	Usually sees nothing, because no role permits any row.
Static rule	`[Region] = "East"`
Dynamic rule	`[UserEmail] = USERPRINCIPALNAME()`, against a mapping table.
USERNAME vs USERPRINCIPALNAME	In Desktop USERNAME gives DOMAIN\User. In the Service both give the UPN.
The dangerous else	A fall-through of TRUE hands the table to any typo. End on FALSE.
What View As proves	The rule and its propagation. Not the workspace role, not another person's identity.
Filter propagation	Active relationships only. Security both ways is a separate checkbox.
Where to put the rule	On the dimension table, and let it flow to the fact table.
What RLS cannot hide	Columns and measures. That is object-level security.
Role names	No commas allowed.

The one habit to keep. Read the workspace access list before you trust the rule. Anyone on it above Viewer sees every row, and no amount of correct DAX changes that. It is the first thing to check and the last thing anyone thinks of, and it takes thirty seconds.

One last thought, and I would genuinely like other people's answers. The version of this I found hardest to explain afterwards was a model where the rules were perfect and three people had been added to the workspace as Contributors during a handover, for two weeks, eighteen months earlier. Nobody had taken them out and nobody had noticed, because nothing about the reports looked different. What is the longest one of these has been open in a tenant you inherited?

References

Saltzer, J. H., & Schroeder, M. D. (1975). The protection of information in computer systems. Proceedings of the IEEE , 63(9), 1278–1308.
Butler, A. C. (2010). Repeated testing produces superior transfer of learning relative to repeated studying. Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133.
Microsoft. Row-level security (RLS) with Power BI, and Row-level security (RLS) guidance in Power BI Desktop, and USERPRINCIPALNAME function (DAX). Microsoft Learn. All quotations above are taken from these pages.

Originally published on Analyst Prep Kit: Row-Level Security in Power BI: Who Gets Filtered and Who Does Not

If it was useful: Buy Me a Coffee.

Absolute vs Relative References in Excel: What the Dollar Sign Locks

Michael Nocito — Sun, 06 Sep 2026 13:00:06 +0000

By Michael Nocito, data analyst · Published August 10, 2026

By the end of this page you can copy a formula anywhere in a sheet and say in advance which parts of it will move. You will be able to pin a rate so a whole column can use it, fill a nine-cell grid from a single formula, and recognize the copied column that is wrong even though its first row is right. It takes about twenty minutes. Every number below is printed next to the arithmetic that produced it, so you can check any of them.

Here is the move. When a formula points at a cell that must not move as the formula is copied, put a dollar sign in front of both halves of that address: =D2*$G$1. Click the reference in the formula bar and press F4 to put the signs in. That is the whole mechanic. A dollar sign means "this part stays where it is".

The short version: Excel does not store the address you typed. It stores the distance from your formula to the cell it points at. A dollar sign replaces that distance with a fixed address.

That is the idea everything else on the page rests on, so it gets the picture.

The original carries a diagram here. In words: Two diagrams sit side by side. Each one shows a short column of four formula cells on the left and a matching column of four target cells on the right, with arrows running between them. The target cells are drawn identically in both diagrams: the top one is shaded because it holds a value, and the three below it have dashed empty outlines because nothing is stored in them. The only difference between the two diagrams is where the arrows go. Above the left diagram the formula reads equals D2 times G1, with no dollar signs, and its four arrows run straight across in parallel, each one reaching a different target cell, so three of the four land on empty outlines. Above the right diagram the same formula appears with dollar signs, reading equals D2 times dollar G dollar one, and its four arrows all curve upward and meet at the single shaded cell at the top, leaving the three empty ones untouched. Four separate landings on the left, one shared landing on the right.

Every number on this page is checkable. The worked example uses the sixteen-row orders table that runs through this set of guides, and every result is printed beside its arithmetic in the form 1,200 × 0.08 = 96.00. If a number here does not match the arithmetic next to it, I got it wrong and you caught it.

1. The fork: should this part move, or stay?

Before the explanation, one question worth answering from memory. Think of a formula you have dragged down a column. Say which parts of it needed to point somewhere new on each row, and which parts needed to keep pointing at the same place.

That question is the whole decision, and it gets asked once per reference in the formula. There are two possible answers.

Answer one: it should move. The reference points at something that belongs to this row. The amount for this order, the date of this order, the name on this line. If that is true, you want the reference to shift as the formula is copied, and you leave it alone. This is called a relative reference, and it is what you get by default.

Answer two: it should stay. The reference points at one thing that every row shares. A commission rate, a tax percentage, an exchange rate, the top-left corner of a lookup table. If that is true, you want every copy of the formula to ask the same cell, and you write it as $G$1. This is an absolute reference.

What decides between them is not the formula and not the cell. It is the sentence you would say out loud about that reference. "The amount on this row" moves. "The rate" stays.

It matters because getting it backwards does not usually produce an error message. It produces a number, and the next section is about why that number is often believable.

2. What a relative reference actually stores

Here is the fact that makes the rest of the page obvious rather than memorized. Type =D2 into cell E2. Excel does not remember "D2". It remembers "one cell to my left".

Copy that formula to E5 and it still means one cell to my left, which is now D5. The formula did not know it said D2. It only ever knew the distance.

Say it out loud, because it is the sentence the whole topic hangs off: a reference is a direction and a distance, not an address.

That is exactly why filling a column works at all. You write one formula for row 2 and copy it to row 17, and each copy quietly points at its own row, because "one cell to my left" is true on every row.

A dollar sign switches off that behavior for the half of the address it sits in front of. $G means column G, always, no matter where this formula ends up. $1 means row 1, always. Put both in and the reference stops being a distance and becomes a place.

3. The rate in one cell, and the 513.60 it cost

Here is the failure, worked end to end on real numbers.

The orders table sits in A1:D17. Column D holds the amount. Off to the side is a small block of assumptions, which is where a careful person puts rates so they are visible and editable instead of buried inside formulas.

Cell	Label	Value
G1	Commission rate	0.08
G2	Tax rate	0.20
G3	Uplift factor	1.05

Now commission per order goes in column E. In E2 you write =D2*G1 and fill it down to E17.

The first row is right. E2 reads 96.00, and 1,200 × 0.08 = 96.00. So the formula gets a nod and the fill gets trusted.

Here is what the next three rows did.

Cell	What it became	Arithmetic	Result
E2	`=D2*G1`	1,200 × 0.08	96.00
E3	`=D3*G2`	950 × 0.20	190.00
E4	`=D4*G3`	1,480 × 1.05	1,554.00
E5	`=D5*G4`	720 × nothing	0.00
E6:E17	`=D6*G5` and down	each amount × nothing	0.00

The column totals 1,840.00. It should total 1,326.40 , because the sixteen amounts add up to 16,580 and 16,580 × 0.08 = 1,326.40. The sheet is over by 513.60, and 1,840.00 minus 1,326.40 = 513.60.

Before reading on, say why row 2 came out right. Getting this in your own words is worth more than the fix itself.

Row 2 is right because row 2 is the row you wrote the formula for. The distance from E2 to G1 is up one, right two. On row 3 that same distance lands on G2, which is the tax rate. On row 4 it lands on the uplift factor. From row 5 down it lands in empty space, and an empty cell is treated as zero in arithmetic.

Two of those wrong numbers, 190.00 and 1,554.00, look like money. That is the part worth remembering. This mistake does not announce itself, and the one row a reviewer usually checks is the one row that is correct.

The fix is one keystroke. In E2, click on the G1 and press F4, which turns it into $G$1, then fill down again.

=D2*$G$1

Now every row asks G1, the column totals 1,326.40, and changing the rate in G1 updates all sixteen rows at once. That second part is the real reward. The rate is in a cell someone can type into, which is what makes the sheet a model instead of a printout.

4. F4, and the four states in order

You almost never type dollar signs. You put the cursor on a reference and press F4, which cycles through the four possible states. Microsoft documents the order, and it is worth knowing because it means one extra press gets you where you want rather than starting over.

Press	Looks like	What is locked	Use it when
1	`$A$1`	Column and row	One cell every copy must ask. Rates, thresholds, a lookup range.
2	`A$1`	Row only	Filling across and down, where the anchor lives in one row.
3	`$A1`	Column only	Filling across and down, where the anchor lives in one column.
4	`A1`	Nothing	Back to a plain relative reference.

On a Mac the same toggle is Cmd+T, and F4 also works if your function keys are set up for it.

One thing that confuses people on Windows: F4 only cycles references when the cursor is on a reference inside a formula. Anywhere else it repeats your last action, which is a genuinely useful key for a different job.

5. Mixed: lock one half, free the other

Before the explanation, a prediction. If $G$1 pins both halves and G1 pins neither, say what you think G$1 pins.

The row. G$1 is free to move sideways and stuck on row 1. Its mirror, $G1, is stuck in column G and free to move up and down.

These are called mixed references, and they exist for one situation: a formula that gets copied in two directions at once. When you only ever fill down a column, relative and fully absolute cover everything you need. The moment you fill across as well, you usually want one half of the address pinned and the other half loose.

The rule of thumb is short. Pin the part that must not change in the direction you are copying.

Your anchors sit in a row across the top, and you copy down: lock the row, G$1.
Your anchors sit in a column down the side, and you copy across: lock the column, $D2.
Both at once is the grid in the next section, and it needs one of each.

6. Nine answers from one formula

Finance wants the commission bill at three possible rates, for the first three orders, so they can pick one. That is nine numbers. It is also one formula.

Put the three rates across the top, in J1, K1 and L1: 0.06, 0.08 and 0.10. The amounts are already down column D. In J2 write this, then fill it right to L2 and down to row 4.

=$D2*J$1

Read it as two decisions rather than as punctuation. $D2 is locked to column D and free on the row, because the amount is always in D but changes row by row. J$1 is locked to row 1 and free on the column, because the rate is always in row 1 but changes column by column.

Here is what the nine cells hold, with the arithmetic.

Amount	At 0.06	At 0.08	At 0.10
1,200	72.00 (1,200 × 0.06)	96.00 (1,200 × 0.08)	120.00 (1,200 × 0.10)
950	57.00 (950 × 0.06)	76.00 (950 × 0.08)	95.00 (950 × 0.10)
1,480	88.80 (1,480 × 0.06)	118.40 (1,480 × 0.08)	148.00 (1,480 × 0.10)

Nine correct answers, one formula, typed once. If you get a grid like this wrong, the symptom is recognizable: the whole first row or the whole first column is right and everything else is nonsense, because the one copy you checked was the one that needed no shifting.

Now picture this on your own numbers. Take a table you actually use, put two or three scenario values across the top of a blank area, and imagine which half of each address you would pin. You do not have to build it. Just decide, for one formula, which two dollar signs you would place and where.

7. Four things the dollar sign does not do

Each of these catches somebody, and three of them are only visible after the damage.

It does not change the answer where you typed it. =D2*G1 and =D2*$G$1 return exactly the same number in E2. The difference appears only when the formula is copied somewhere else. That is why testing one cell proves nothing about a fill.

It does not protect the cell from editing. Locking a reference and locking a cell are unrelated. Stopping people from typing over G1 is sheet protection, which is a different feature in a different menu.

It does not stop a reference changing when the target physically moves. This one surprises people who think absolute means permanent. Insert a row above row 1, and every $G$1 in the workbook quietly rewrites itself to $G$2, because Excel is following the value you pointed at. Absolute means "does not shift when the formula is copied", not "never changes".

It does not survive a cut. Cutting the anchor cell with Ctrl+X and pasting it elsewhere drags every reference to it along, dollar signs and all. Copying it does not. If a model breaks the day someone tidied the layout, this is usually why.

8. When you need no dollar sign at all

There are two ways to get the same protection without any punctuation, and both read better afterwards.

Name the cell. Select G1, type CommissionRate into the Name Box beside the formula bar, and press Enter. Now the formula is =D2*CommissionRate, which is already absolute, and which says what it means to anyone who opens the file next year. Defined names do not shift when a formula is copied.

Put the data in a table. Inside an Excel Table created with Ctrl+T, a column reference is written [@Amount] and it means "the amount on this row" by name rather than by distance. There is nothing to lock and nothing to get backwards, and the reference keeps working when columns are inserted. This is one of the better reasons to press Ctrl+T on a range you are about to write formulas against.

Neither one removes the need to understand the dollar sign, because you will read other people's workbooks for the rest of your career. But for the sheets you build, a named rate is easier to be right about than a pinned address.

The full before and after

Same table, same rate, same fill. One character apart.

	Before: `=D2*G1`	After: `=D2*$G$1`
Row 2	96.00	96.00
Row 3	190.00 (used the tax rate)	76.00 (950 × 0.08)
Row 4	1,554.00 (used the uplift)	118.40 (1,480 × 0.08)
Rows 5 to 17	0.00 (pointed at empty cells)	Each amount × 0.08
Column total	1,840.00	1,326.40
Change the rate in G1	Three rows react, thirteen do not	All sixteen rows react
What a reviewer sees first	A correct first row	A correct first row

The last line is the point of the whole page. Both versions pass the check most people run.

Edge cases that break a copied formula quietly

These are the ones that produce a number rather than an error.

A lookup range with no dollar signs. =VLOOKUP(A2,H2:I50,2,FALSE) filled down becomes H3:I51, then H4:I52. The range slides off the bottom of your lookup table and the last rows return not-found for no visible reason. Lock it: $H$2:$I$50. There is more on this in VLOOKUP vs XLOOKUP.
Filling right when you only tested down. A column-only test passes, then somebody drags the same formula sideways and the row reference was never pinned. Test a fill in both directions if the formula might be copied in both.
A mixed reference on the wrong axis. $D2 where you meant D$2 still returns numbers. They are just the wrong numbers, taken from a plausible neighbor.
Rows inserted inside a locked range. $H$2:$I$50 expands to $H$2:$I$51 when a row is inserted inside it, which is usually what you want. Rows added at the bottom, outside the range, are not picked up at all. A table reference avoids the whole question.
Conditional formatting uses the same mechanism. A formula rule is written once for the top-left cell of the selection and filled across the rest, which is why one dollar sign turns a highlighted cell into a highlighted row.
The value that never moves anyway. If a rate appears in exactly one formula and nowhere else, a dollar sign is not the fix for anything. The fix is putting the rate in a cell in the first place, so it is visible and editable.

Why this works

The mechanism itself is a design decision with an obvious payoff: storing an offset instead of an address is what lets one typed formula serve sixteen rows. Everything in this topic, including the failure, comes out of that single choice.

The failure is worth taking seriously, because reference mistakes are one of the plainest examples of a general finding about spreadsheets. They are common, they produce plausible output, and inspection catches fewer of them than people expect. When undergraduate students inspected a spreadsheet seeded with deliberate errors, working alone for forty-five minutes, they found 63% of them on average, and groups of three working together found 83% (Panko, 1999, Journal of Management Information Systems , 16(2), 159–176). A single reviewer glancing at one row is a long way below either number. The broader review of this literature is worth reading if you build models anybody relies on (Panko, 1998, Journal of End User Computing , 10(2), 15–21).

That is the argument for the habit at the end of this page rather than for being more careful. Care is not a control. A total you predicted before you looked is a control, and it is the reason this page keeps printing the arithmetic beside every number.

One note on the cheat sheet below. It is built to be covered and recalled rather than read, because testing yourself on material transfers to new situations better than restudying it, which matters here since you will be applying this to sheets that look nothing like the one above (Butler, 2010, Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133).

Using this on your own workbook

Auditing every reference in an inherited file is miserable and you will give up on the second sheet. Do this instead, in order.

Find the numbers typed inside formulas. Anything like *0.08 or *1.2 sitting in a formula is a rate with nowhere to live. Move it to a labeled cell first. The dollar sign question does not even arise until the value has a home.
Say the sentence out loud for each reference. "The amount on this row" moves. "The rate" stays. If you cannot say which one it is, that reference is the one to check.
Use F4 rather than typing the signs. Fewer keystrokes and no chance of putting one in front of the wrong half.
Check the last row, not the first. The first row of a fill is the one that is right in both the working and the broken version. Scroll to the bottom and read one formula there.
Predict the column total before you look at it. Sixteen orders at eight percent should land near 1,300. A total of 1,840 fails that test in one second, and there is more on where these checks belong in checking your work before anyone else does.
Name the anchors you keep reusing. A rate used in four formulas is worth a name. A rate used once is not.

If you have paper nearby, one optional sketch locks this in for good. Draw two columns of four boxes. On the left write one formula at the top and draw an arrow from each box to where it points when copied down. Do it once with the arrows fanning out to four different boxes, and once with all four arrows meeting at the top box. Then label which picture needed the dollar signs. You will not need to look this up again.

More detail on this, and more like it. Every how-to sits in one place on the guides index: Excel, SQL, Python, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Thing	What it does
What a reference stores	A direction and a distance, not an address.
`A1`	Relative. Moves with the copy, both ways.
`$A$1`	Absolute. Every copy asks the same cell.
`A$1`	Row locked, column free. Anchors sitting in one row.
`$A1`	Column locked, row free. Anchors sitting in one column.
F4 order	`$A$1`, `A$1`, `$A1`, `A1`. Mac: Cmd+T.
The question to ask	"The amount on this row" moves. "The rate" stays.
Grid, filled both ways	`=$D2*J$1`. One of each, one formula, nine answers.
Why it survives review	The first row is correct in the broken version too.
Row 3 reading 190.00	The reference walked onto the next assumption down.
Rows reading 0.00	The reference walked into empty cells. Empty is zero.
Inserting a row	Rewrites `$G$1` to `$G$2`. Absolute is not permanent.
Cutting the anchor	Drags every reference with it. Copying does not.
Lookup range	Lock it, or it slides off the bottom of the table.
No dollar sign needed	A defined name, or `[@Amount]` inside a table.
Which row to check	The last one. The first one is right either way.

The one habit to keep. Predict the total before you read it. Sixteen orders at eight percent is roughly thirteen hundred, so 1,840.00 is wrong before you have looked at a single formula. A rough number you committed to in advance catches reference mistakes faster than reading the formulas will, and it costs about four seconds.

One last thought, and I would genuinely like other people's answers. The most expensive version of this I have seen was a forecast where the growth rate was pinned correctly and the base year was not, so every column quietly grew from the wrong starting point and the shape of the chart still looked right. What is the longest one of these has gone unnoticed in a file you inherited?

References

Panko, R. R. (1999). Applying code inspection to spreadsheet testing. Journal of Management Information Systems , 16(2), 159–176.
Panko, R. R. (1998). What we know about spreadsheet errors. Journal of End User Computing , 10(2), 15–21.
Butler, A. C. (2010). Repeated testing produces superior transfer of learning relative to repeated studying. Journal of Experimental Psychology: Learning, Memory, and Cognition , 36(5), 1118–1133.

Originally published on Analyst Prep Kit: Absolute vs Relative References in Excel: What the Dollar Sign Locks

If it was useful: Buy Me a Coffee.

Tableau Hierarchies: One Drag Puts a Drill-Down on Every Chart

Michael Nocito — Sat, 05 Sep 2026 13:00:14 +0000

By Michael Nocito, data analyst · Published August 9, 2026

By the end of this page you can build a hierarchy in one drag, put it in the right order first time, and say exactly how it differs from a group, a set and a bin. Those four get confused constantly, and one sentence tells them apart for good. It's about twelve minutes.

Here's the thing to do today. In the Data pane, drag Sub-Category and drop it directly on top of Category. Tableau asks for a name, and from that moment every view built on Category carries a small plus icon. Click it and the chart expands into sub-categories, in place, without you building a second sheet.

The short version: a hierarchy organizes fields into levels. A group, a set and a bin organize the values inside one field. That's the whole distinction.

What a level actually costs you is the part people are surprised by, so it gets the picture.

The original carries a diagram here. In words: Three tiers stacked vertically and widening as they descend. The top tier shows three wide boxes labeled Category. The middle tier shows nine narrower boxes labeled Sub-Category, with lines fanning out from each box above to three below it. The bottom tier shows a long row of many very thin boxes labeled Product, again fanning out from each box above. A plus symbol sits at the left of the top tier and a minus symbol at the left of the bottom tier, marking the drill controls that move between them. The widening shape shows that every step down the hierarchy multiplies the number of marks in the view, so the bottom level holds far more rows than the top.

This is on the certification. Hierarchies sit in Section 2, Exploring and Analyzing Data, which is 37% of the Tableau Desktop Foundations exam and the largest section on it. The certification kit drills them alongside groups, sets and bins, which is where the wrong answers usually come from.

1. What a hierarchy actually is

Before the explanation: you build a hierarchy from Category and Sub-Category. What happens to the two original fields?

Nothing. They're both still there, still usable on their own, now sitting inside a named hierarchy in the Data pane. A hierarchy doesn't consume the fields it's made of.

That's the first thing to hold onto. A hierarchy is an arrangement of existing fields into levels, ordered from broad at the top to specific at the bottom. It creates no new field and changes no data. It records a relationship you already knew about: every sub-category belongs to exactly one category.

What you get in exchange is a control. Once a hierarchy field is in a view, Tableau puts a small plus or minus icon on the pill, and clicking it adds or removes a level of detail in place. The chart you already built becomes a chart the reader can open up.

That is a bigger deal than it sounds, because the alternative is building a second sheet. Without a hierarchy, "sales by category" and "sales by sub-category" are two views someone has to navigate between. With one, they're the same view at two depths.

The sentence to remember. A hierarchy organizes fields into levels and hands the reader a drill control. It does not change the fields, and it does not change the data.

2. Building one, and getting the order right

Before the steps: you drop Category onto Product instead of the other way round. What does the reader get?

A drill that starts specific and gets vaguer, which reads as broken even though nothing errored. Order is the only decision in building a hierarchy, and it's the one people get wrong.

Building it, per Tableau's documentation:

In the Data pane, drag a field and drop it directly on top of another field.
Tableau asks you to name the hierarchy. Give it a name a reader would recognize.
Drag additional fields into the hierarchy as needed, to add more levels.
Re-order fields in the hierarchy by dragging them to a new position.

If the field you want lives inside a folder, the drag is awkward, so use the other route: right-click the field and select Create Hierarchy.

Now the order rule, and it's one line. Coarse at the top, fine at the bottom. Each level down must be contained by the level above it.

Say out loud why containment is the test, before reading on. It's because drilling means "show me what this is made of". Category contains sub-categories, so opening a category to reveal its sub-categories makes sense. Product does not contain categories, so the reverse drill has nothing sensible to reveal, and Tableau will still let you build it.

Hierarchy	Order	Works because
Product	Category → Sub-Category → Product Name	Each level is contained by the one above
Location	Country → State → City → Postcode	Same containment, and the one everyone recognizes
Date	Year → Quarter → Month → Day	Tableau builds this one for you on any date field
Org	Region → Manager → Rep	Contained, as long as a rep reports to one manager

That last row carries the caveat worth knowing. Containment has to be true in your data, not just in the org chart. If a rep is shared across two managers, drilling will show their sales under both, and the levels will not add up to the total. Check before you build.

3. Hierarchy against group, set and bin

Before the table: hierarchies, groups, sets and bins all sound like ways of organizing data. What single question separates them?

Whether they work on fields or on the values inside one field. A hierarchy is the only one of the four that arranges several fields. The other three reshape the values inside a single field, and they do it in three different ways.

Tool	Works on	What it does	What you get
Hierarchy	Several fields	Orders them into levels	A drill control. Nothing changes until someone clicks.
Group	One field's members	Combines several members into one bigger member	Fewer, coarser categories
Set	One field's members	Labels every member IN or OUT	Two groups you can compare
Bin	One measure's values	Cuts a continuous range into equal-width buckets	A dimension you can count, like a histogram

Two of those pairs get confused more than the others, so here are both in a sentence.

Hierarchy against group. They feel similar because both make things coarser. A group makes Sub-Category coarser by merging its members. A hierarchy leaves Sub-Category alone and lets a reader step up to Category, which was already a separate field. If the coarser level already exists as a field, you want a hierarchy. If it doesn't exist and you have to invent it, you want a group.

Set against group. A group combines members into bigger members and everything stays in the view. A set splits members into IN and OUT so you can compare the two halves. Different jobs entirely, which the sets guide goes through properly.

Picture your own data pane for a moment. How many of your dimensions are really levels of one thing that nobody has connected yet? Region and Country. Manager and Rep. Those pairs are hierarchies waiting to be dragged together.

4. What the plus and minus controls do

Before the explanation: you click the plus icon on a hierarchy pill. Does the view replace the current level or add to it?

It adds. Tableau's wording is that you can "drill up or down in the hierarchy to add or subtract more levels of detail", and the key word is add. Category stays on the shelf and Sub-Category joins it, so you get sub-categories nested inside their categories rather than a flat list of sub-categories.

That distinction is worth a moment. Drilling down gives you both levels, with the parent still grouping the children. If you wanted only sub-categories, you don't drill, you swap the field.

Two practical consequences follow from the picture at the top of this page.

Marks multiply, they don't shift. Three categories drilling into nine sub-categories is three times the marks. Drilling again into thirty-nine products is thirteen times the original. That's fine on a bar chart and painful on a map or a scatter plot, so check what the chart does at the bottom level before you ship the drill.

Build the view at the level you want people to land on. The default state is whatever you saved, and most readers never click anything. So save it at the top level, and let the drill be for the people who want more.

5. Details people miss

Before the list: someone says removing a hierarchy will delete the fields inside it. Are they right?

No, and this is the fact that stops people experimenting. Right-click the hierarchy and select Remove Hierarchy. Tableau's own description: "The fields in the hierarchy are removed from the hierarchy and the hierarchy disappears from the Data pane." The fields go back to being ordinary fields. Nothing is lost, so a hierarchy is safe to try.

Five more worth knowing.

Date fields get a hierarchy automatically. Year, quarter, month, day. You don't build it, and the plus and minus on a date pill are doing exactly what they do on one you made yourself.

A hierarchy belongs to the data source, not the sheet. Build it once and every worksheet on that data source can use it. This is the same arrangement as sets, and it's the reason both live in the Data pane.

You can drill into one member instead of all of them. Clicking the plus on the pill opens every category. Clicking the plus on a single header opens just that one, which is usually what a reader actually wanted.

Levels can be reordered after the fact. Drag a field to a new position inside the hierarchy. You do not have to remove it and start again, which is what most people do the first time.

A hierarchy does not enforce anything. It records a containment you believe is true. If your data has a sub-category appearing under two categories, Tableau will show it under both without complaint, and your levels will not sum to the total. That's a data problem the hierarchy will faithfully display rather than catch.

Why this works

Drilling isn't a Tableau invention, it's a general answer to a problem every large dataset has: the view that shows everything shows nothing. Shneiderman set out the pattern that most interactive tools still follow, summarized as overview first, zoom and filter, then details on demand (Shneiderman, 1996, Proceedings of the 1996 IEEE Symposium on Visual Languages , 336–343). A hierarchy is that pattern made into one control. The overview is the top level, and the detail arrives only when someone asks for it.

The reason this beats building two sheets is about where the effort sits. Two sheets ask the reader to notice that a second view exists, find it, and hold the first one in memory while looking at it. One view with a drill asks them to click a plus. The information is the same and the work of connecting the two levels has moved from the reader to the tool.

The product behavior on this page comes from Tableau's own documentation, which is the authority on it. Create hierarchies (Tableau Help, current version) is the source for the drag-and-drop creation step, the ability to add and re-order fields, the plus and minus drill controls, the Create Hierarchy route for fields inside folders, and the statement about what Remove Hierarchy does to the fields. Where a secondary write-up disagrees with that page, the page wins.

Using this on your own workbook

Open your Data pane and read the dimension list. How many pairs are levels of the same thing, sitting next to each other unconnected?

Doing this to a whole workbook at once is dull and you'll stop. Take one, in this order.

Find a pair where one contains the other. Region and country. Category and sub-category. Manager and rep. If neither contains the other, it isn't a hierarchy.
Check the containment in the data, not the org chart. Count the distinct parents per child. More than one means drilling will double-count, and you have a data question before you have a hierarchy.
Drag the finer field onto the coarser one. Fine onto coarse. Getting this backwards is the whole failure mode.
Name it for a reader, not for you. "Product hierarchy" is fine. The name shows up in the Data pane and other people will read it.
Save the view at the top level. Most readers never drill, so the state you save is the state almost everyone sees.
Click down to the bottom level once, and look. If the chart is unreadable at full depth, either the chart type is wrong or that level does not belong in the hierarchy.

If you have paper nearby and five spare minutes, there's one drawing worth doing and it's optional. Write your three levels as three rows, then write the number of distinct values in each row next to it. Seeing 3, 9 and 39 in your own handwriting is what makes the bottom-level chart problem obvious before you build it.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Concept	What it does
Hierarchy	Orders several fields into levels and hands the reader a drill control
How to build one	Drag a field and drop it directly on top of another field in the Data pane
Field inside a folder	Right-click the field and select Create Hierarchy
Adding levels	Drag more fields into the hierarchy
Reordering levels	Drag a field to a new position inside the hierarchy
Order rule	Coarse at the top, fine at the bottom. Each level contained by the one above.
Plus and minus icons	Drill up or down, adding or subtracting a level of detail
Drilling adds	The parent level stays on the shelf. You get both, nested.
Removing a hierarchy	Right-click, Remove Hierarchy. The fields survive as ordinary fields.
Where it lives	The Data pane, so it belongs to the data source and works on every sheet
Date fields	Get year, quarter, month and day automatically. No build needed.
Hierarchy against group	Hierarchy arranges fields. A group merges members inside one field.
Hierarchy against set	A set labels members IN or OUT of one field. No levels involved.
Hierarchy against bin	A bin cuts a continuous measure into equal-width buckets
What it does not do	Enforce containment. Bad data drills into double counting silently.

The one habit to keep. Before you build a hierarchy, count the distinct parents for each child. One parent means it's a hierarchy. More than one means drilling will double-count, and you've found a data problem worth more than the drill control was.

One last thought, and I'd like other people's answers. What made these click for me was the fields-against-values line: a hierarchy is the only one of the four that touches more than one field, and once that landed I stopped mixing it up with groups. What's the pair of Tableau features you had to keep looking up before something finally separated them?

References

Tableau. Create hierarchies. Tableau Desktop and Web Authoring Help , current version. help.tableau.com. The authority for the product behavior described here.
Shneiderman, B. (1996). The eyes have it: A task by data type taxonomy for information visualizations. Proceedings of the 1996 IEEE Symposium on Visual Languages , 336–343.

Originally published on Analyst Prep Kit: Tableau Hierarchies: One Drag Puts a Drill-Down on Every Chart

If it was useful: Buy Me a Coffee.

Tableau Dashboard Extensions: What They Add, and What They Can Read

Michael Nocito — Sat, 05 Sep 2026 13:00:06 +0000

By Michael Nocito, data analyst · Published August 9, 2026

By the end of this page you can add an extension to a dashboard, tell the two hosting kinds apart, and read the permission box well enough to know what you're agreeing to. You'll also know the one behavior that surprises people after publishing, which is what an extension looks like in a PDF. It's about twelve minutes.

Here's what to do before you add your first one. Find out where it runs. An extension you drop onto a dashboard is a web application, and some of them are hosted on Tableau-managed servers while others are hosted by whoever built them. That single fact decides how much thought the rest of the decision needs.

The short version: an extension is a third-party web application running inside a dashboard object, and one of the two permission levels gives it your full underlying data along with table and field names.

Where the code actually runs is the thing the panel doesn't show you, so it gets the picture.

The original carries a diagram here. In words: A large rectangle labeled your dashboard contains four panels that all look alike. Three of them are shaded the same and marked as ordinary views. The fourth, in the lower right and outlined in a warning color, is labeled extension. A line runs from that fourth panel, crosses the boundary of the dashboard rectangle, and continues out to a separate box drawn outside and to the right labeled third-party host. The three ordinary views have no lines leaving the rectangle. The drawing shows that the extension panel sits inside the dashboard visually while its code and its data traffic reach outside it, which the other three panels never do.

1. What an extension actually is

Before the explanation: you drop an extension onto a dashboard and it draws a chart type Tableau doesn't have. Where did that chart come from?

From a web application, written by somebody else, running inside a panel on your dashboard. Tableau's own description is that extensions "let you add unique features to dashboards or directly integrate them with applications outside Tableau," and that they are "web applications created by third-party developers."

That's a bigger idea than a plugin. A worksheet is Tableau drawing your data. An extension panel is a separate application that Tableau has given a rectangle to, and which can be given access to the data behind the dashboard.

The things people use them for are genuinely useful and genuinely outside what a worksheet does: write-back to a database from inside the dashboard, chart types Tableau doesn't ship, integration with a planning or ticketing tool, or a control that changes several sheets at once in a way parameters can't.

Extensions are added as a dashboard object, which puts them in the same family as a text box, an image or a web page object rather than in the same family as a sheet.

The sentence to remember. An extension is somebody else's application borrowing a rectangle on your dashboard. Everything else on this page follows from taking that literally.

2. The two kinds, and where the code runs

Before the distinction: two extensions do the same job and look identical on the dashboard. What could make one a much easier decision than the other?

Who is hosting it. Tableau splits extensions by exactly that, and it's the first thing to establish about any one you're considering.

Kind	Where it runs	What that means for you
Tableau Trusted	Tableau-managed hosts. Reviewed by Tableau, and includes ones built by Tableau and by Exchange Partners through Tableau's review service.	Someone with a stake in it has looked at the code, and the hosting is not a stranger's
Network-enabled	A third-party host. The developer manages delivery without Tableau in the middle.	Your judgment about that developer is the whole safeguard

There's also a sandboxed option, which runs an extension in an isolated environment with no access to the network outside. It's the tightest of the choices, and it needs Tableau Server 2019.4 or later.

Say out loud why hosting matters more than what the extension displays, before reading on. It's because the panel's appearance is authored by the same people you'd be trusting. A polished panel is evidence of design effort and nothing else. Where it runs is a fact about the software rather than a claim by it.

None of that makes network-enabled extensions a bad choice. Plenty of good tools are delivered that way, and being outside Tableau's review service is not a mark against a developer. It means the question moves to you, and the question is the ordinary one you'd ask about any vendor handling company data.

3. What the permission box is really asking

Before the answer: an extension asks for permission and you click allow. What did you just agree to?

It depends which of two things it asked for, and the difference is large. Tableau's model has you "allow or deny the dashboard extension access to data in the workbook," and a network-enabled extension may request the higher level, called Full Data Access.

Full Data Access includes access to full underlying data, plus table and field names from the data sources. Read that as a sentence about your database rather than about your chart. Field and table names are the shape of your systems, and underlying data is the rows themselves rather than the totals on screen.

What it can reach	Summary data	Full Data Access
The aggregated numbers shown in the view	Yes	Yes
The underlying rows behind those numbers	No	Yes
Table and field names from the data source	No	Yes

The practical rule falls out of the table. An extension that draws a chart of what's already on screen has no need for the underlying rows. If one asks for Full Data Access and its job is drawing a picture of a summary, that mismatch is the thing to ask about, and asking is reasonable rather than paranoid.

If you change your mind later, Tableau has Reset Permissions, so an early allow is not permanent. Worth knowing before you spend an afternoon worrying about a click you already made.

Picture the dashboards you've built at work. If one of their extensions could read every underlying row, whose data would that be, and would they know? That question is the whole of this section.

4. Adding one, and what it needs to work

Before the steps: your extension panel is blank on a colleague's machine and fine on yours. What's the most likely cause?

Usually one of two things, and both are in this section. Adding an extension is three steps.

Open a dashboard sheet in a Tableau workbook.
Drag an Extension object from the Objects section onto the dashboard.
Either search for an extension, or browse locally for a .trex file you've already downloaded.

That .trex file is the manifest, meaning it's the small file that tells Tableau where the extension lives and what it's called. It isn't the extension itself, which is the point people miss when they wonder why such a small file added such a large feature.

Three requirements, each of which produces a blank or missing panel when unmet.

JavaScript must be enabled in Tableau Desktop. It's a dashboard setting, and an extension cannot run without it.

An administrator can disable extensions entirely. On Tableau Server and Tableau Cloud this is a setting someone else controls, so an extension that works on your machine can be blocked in the environment you publish to. Check before you design a dashboard around one.

Sandboxed extensions need Tableau Server 2019.4 or later. An older server is a hard stop rather than a degraded experience.

Then the one that catches people after publishing. On Tableau Server and Tableau Cloud, extension objects appear blank in prints, PDFs and images. So a dashboard that gets exported for a monthly pack has a blank rectangle where the extension was, and nobody finds out until the pack is circulated.

5. When to reach for one, and when not to

Before the list: an extension would solve your problem in ten minutes. What's worth checking before you use it?

Whether Tableau already does it, because a native answer costs no permission decision, no hosting question and no blank box in the PDF.

Extensions earn their place when the job is genuinely outside Tableau: writing data back to a source, a chart type that isn't in the Show Me options, or connecting the dashboard to another system your team works in. That's real capability and there's no native substitute.

Three cases where the native route is the better answer, and all three are common.

What you want	Native answer
One control that drives several sheets	A parameter, or a dashboard action
Readers changing which group is highlighted	A set action, which updates set membership from the dashboard
Drill-down from summary to detail	A hierarchy, which puts the control on the pill for free

Two more things worth deciding before you commit, both about the dashboard rather than the extension. An extension panel occupies space, and space on a dashboard is the scarce thing: the two or three views guidance applies to a panel that isn't a view either. And an extension is a dependency, so a dashboard built around one stops working properly if the developer stops maintaining it.

Why the permission box is the hard part

The technical steps here are easy and the judgment is not, and that split is worth naming because it's where the real risk sits. The problem isn't that the permission box is hidden. It's shown, clearly, at the moment you add the extension. The problem is that permission prompts are a weak way to get a good decision out of anyone.

Felt and colleagues studied exactly this on Android, where users see a permission list before installing an application. Their conclusion was that the permission warnings did not help most users make correct security decisions, while a notable minority did both notice them and understand them reasonably well (Felt, Ha, Egelman, Haney, Chin, & Wagner, 2012, Proceedings of the Eighth Symposium on Usable Privacy and Security). The finding generalises past Android because the mechanism is the same: a prompt arrives when someone has already decided they want the thing, and it asks them to weigh a cost they can't see against a benefit they came for.

The fix that follows is not to concentrate harder at the prompt. It's to move the decision earlier, to a moment when you haven't yet committed. Establish where an extension is hosted and what it needs access to before you drag it onto the dashboard, and the box at the end becomes a confirmation of something you already decided rather than the decision itself.

The product behavior on this page comes from Tableau's own documentation, which is the authority on it. Use dashboard extensions (Tableau Help, current version) is the source for the definition of an extension, the trusted and network-enabled distinction, the Full Data Access description, Reset Permissions, the three steps for adding one, the JavaScript requirement, the administrator control, the Tableau Server 2019.4 requirement for sandboxed extensions, and the statement that extension objects appear blank in prints, PDFs and images. Where a secondary write-up disagrees with that page, the page wins.

Using this on your own dashboard

Think about any dashboard you've published with an extension on it. Could you say today who hosts that extension and what data it was granted?

Do this per extension rather than as a project, in this order.

Ask whether Tableau already does it. Parameters, dashboard actions, set actions and hierarchies cover more of the common asks than people expect.
Establish the hosting before you install. Tableau Trusted, network-enabled or sandboxed. This is a fact you can look up, not a judgment call.
Decide what level of access the job actually needs , then compare it with what's requested. A mismatch is a question, not necessarily a problem.
Check your environment allows extensions at all , and check the server version if you need a sandboxed one. Both are hard stops and both are cheap to check first.
Export the dashboard to PDF before you call it finished. The blank rectangle is much better discovered by you than by the person who circulates the monthly pack.
Write down what you granted and why , next to the workbook. Six months later, nobody remembers, and Reset Permissions is a much easier conversation with a note beside it.

If you have paper and five spare minutes, there's one sketch worth doing and it's optional. Draw your dashboard as boxes, then draw a line out of the page from every box whose contents come from outside your organization. Most dashboards produce no lines at all, which is exactly why the one that does deserves a minute of thought.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Concept	What it does
Dashboard extension	A third-party web application running in a dashboard object
What it adds	Features outside Tableau, or integration with an outside application
Tableau Trusted	Reviewed by Tableau and deployed on Tableau-managed hosts
Network-enabled	Hosted by a third party, delivered without Tableau in the middle
Sandboxed	Runs isolated, with no outside network access. Needs Tableau Server 2019.4 or later.
Permission choice	Allow or deny the extension access to data in the workbook
Full Data Access	Full underlying data, plus table and field names from the data sources
Changed your mind	Reset Permissions
Adding one	Drag an Extension object from Objects, then search or browse for a .trex file
.trex file	The manifest. It points at the extension, it is not the extension.
Requirement	JavaScript enabled in Tableau Desktop
Administrator control	Extensions can be disabled entirely on Server and Cloud
In a PDF or image	Extension objects appear blank on Server and Cloud
Reach for native first	Parameters, dashboard actions, set actions, hierarchies
The standing cost	A dependency on someone else continuing to maintain it

The one habit to keep. Decide where an extension is hosted and what access it needs before you drag it onto the dashboard. Making the call while you still have nothing invested is the only reliable way to make it well, and it turns the permission box into a confirmation instead of a decision.

One last thought, and I'd like other people's answers. The detail that changed how I think about these is the blank rectangle in a PDF, because it means the dashboard behaves differently depending on how someone consumes it, and nothing warns you. What's the extension you've actually found worth the dependency?

References

Tableau. Use dashboard extensions. Tableau Desktop and Web Authoring Help , current version. help.tableau.com. The authority for the product behavior described here.
Felt, A. P., Ha, E., Egelman, S., Haney, A., Chin, E., & Wagner, D. (2012). Android permissions: User attention, comprehension, and behavior. Proceedings of the Eighth Symposium on Usable Privacy and Security (SOUPS '12).

Originally published on Analyst Prep Kit: Tableau Dashboard Extensions: What They Add, and What They Can Read

If it was useful: Buy Me a Coffee.

Tableau Desktop Foundations Exam: 40 Questions, and 20 Right Passes It

Michael Nocito — Fri, 04 Sep 2026 13:00:31 +0000

By Michael Nocito, data analyst · Published August 9, 2026

By the end of this page you'll know exactly what the Salesforce Certified Tableau Desktop Foundations exam asks, how it's scored, which version of Tableau it tests, and which of its four sections is worth more than double another. Every figure comes from Salesforce's own exam guide, checked on 9 August 2026. It takes about twelve minutes.

Here's what to do with it today. Before you open another lesson, put the four section weights next to your calendar and split your remaining study hours in the same proportion. Section 2 is 37% of the exam, which works out to about 15 of the 40 scored questions. You need 20 correct to pass. That one section is three quarters of a pass on its own.

The short version: 40 scored questions, 70 minutes, and 48% passes. The four sections are not the same size, and the published weights tell you where your hours belong.

The sizes are the whole reason to read this, so they get the picture.

The original carries a diagram here. In words: Four horizontal bars, one per exam section, each drawn to the length of its published percentage weight. From top to bottom: Connecting to and Preparing Data at 23 percent, Exploring and Analyzing Data at 37 percent and clearly the longest bar, Sharing Insights at 25 percent, and Understanding Tableau Concepts at 15 percent and clearly the shortest. The Exploring bar is roughly two and a half times the length of the Understanding bar. Seen side by side at true scale, the four sections are visibly unequal, which is the point: an hour of study is worth more in some of them than in others.

Where these numbers come from. Salesforce publishes the exam guide as help article 005298988, and that page is the authority. Third-party summaries of this exam disagree with it. One widely repeated version says 45 questions in 60 minutes with a 750 out of 1000 passing score, and all three of those figures are wrong. Check the Salesforce page yourself before you trust any number about this exam, including the ones on this page.

1. The format, in numbers you can plan around

Before the table: 70 minutes and 40 questions. How long is that per question, and does it feel tight to you?

It's about a minute and three quarters each, and for most people it isn't tight at all. Here is the full format.

Thing	Value
Scored questions	40
Unscored questions	Up to 5 more, mixed in unmarked
Question types	Multiple choice and multiple select
Time	70 minutes
Passing score	48% on the English version, 55% on the Japanese version
Registration fee	US$75, plus local tax
Retake fee	Free
Prerequisite	None
Tableau version tested	2022.3

Two of those rows do real work on exam day.

The unscored questions are invisible. You could see 45 questions and only 40 of them count, and nothing on screen tells you which five are which. So a question that feels strange or unusually hard may simply not be scored. Answer it and move on. There's no way to spot them and no benefit to trying.

Every question is multiple choice or multiple select. There is nothing to build. You will not be dropped into a live copy of Tableau and asked to make a chart. That changes how you should practice, and it's the single biggest planning fact on this page. Reading and recognizing beats building, for this exam only.

On timing, work the arithmetic yourself rather than trusting the feeling. 70 minutes is 4,200 seconds. Divide by 45, the worst case where every unscored question shows up, and you get 93 seconds a question. Divide by 40 and you get 105 seconds. Either way you have well over a minute for a question with four short options.

2. Where the marks are, converted into questions

Before the numbers: the four sections are weighted 23, 37, 25 and 15 percent. Which one would you study first?

Percentages are hard to plan against, so turn them into questions. Multiply each weight by the 40 scored questions.

Section	Weight	Arithmetic	Scored questions
1. Connecting to and Preparing Data	23%	0.23 × 40 = 9.2	about 9
2. Exploring and Analyzing Data	37%	0.37 × 40 = 14.8	about 15
3. Sharing Insights	25%	0.25 × 40 = 10	about 10
4. Understanding Tableau Concepts	15%	0.15 × 40 = 6	about 6

Those four add to 40, which is the check that the conversion is right. Salesforce doesn't promise an exact count per section, so treat these as the shape rather than a guarantee.

Now the sentence worth carrying into your study plan. Section 2 is about 15 questions and you need 20 to pass, so section 2 alone is three quarters of a pass. Section 4 is about 6 questions, which is less than a third of one.

Say out loud what that means for an hour of your time, before reading on. An hour spent on exploring and analyzing is worth roughly two and a half hours spent on Tableau concepts, because 37 divided by 15 is about 2.5. This is the hinge of the whole page, and it's the reason to read a weighting table at all.

What section 2 covers is the daily work of the tool: filtering, sorting, grouping, sets, hierarchies, calculations, table calculations, and mapping. If you want a single place to start, start there. That is also why four of the six Tableau guides on this site sit inside that section.

3. What a 48% pass mark actually buys you

Before the answer: 48% of 40 questions. How many can you get wrong and still pass?

Twenty. The arithmetic is 0.48 × 40 = 19.2, and since there is no such thing as a fifth of a question, you need 20 correct. That leaves 20 you can miss.

That is a genuinely forgiving mark, and it's worth knowing for one reason only: it should stop you postponing the booking. People delay a certification for months waiting to feel ready, and 48% is not a mark that requires feeling ready. It requires knowing half the material.

What it should not do is turn into a plan to skip a section. Twenty wrong sounds like a wide margin until you notice that multiple-select questions usually need every box right to score, and that the questions you feel confident about are the ones you're most likely to misread. Aim well above the line, and treat the 48% as permission to book rather than permission to prepare less.

The retake is free, which changes the risk further. A first attempt costs $75 and a second costs nothing, so the real cost of being wrong about your readiness is time, not money.

4. The version it tests, and why that saves you time

Before the explanation: the exam guide says it currently tests on Tableau 2022.3. Why would that be good news?

Because it puts a fence around what you have to learn. Anything Tableau shipped after 2022.3 is outside the exam, so a newer feature you read about in a release note or a recent video is not something you need for this.

That matters more than it sounds. Tableau ships several releases a year, and the tutorials you find by searching are written against whatever version was current when the author wrote them. A 2026 walkthrough of a 2025 feature is good learning and irrelevant preparation.

Two practical consequences. First, if a menu in your copy of Tableau doesn't match a screenshot in your study material, the exam is more likely to follow the older wording. Second, when your notes and a video disagree, Tableau's own help documentation is the tiebreaker, not the video.

Picture your own study list for a moment. How many items on it are features you've only ever seen in a recent release note? Those are the ones to move to the bottom.

5. Details people miss

Before the list: someone tells you a wrong answer costs you marks. Are they right?

No. Nothing in the published guide describes a penalty for a wrong answer, and the score is a percentage of correct responses. So there is never a reason to leave a question blank. Answer every one, including the ones you're guessing at.

Five more worth knowing.

The exam was renamed. This is the exam that used to be called Tableau Desktop Specialist. Older study material, forum posts and course titles still use the old name, and they're usually talking about the same thing. Check the publication date and the section weights before trusting any of it.

Multiple select tells you how many to pick. These questions say how many answers to choose. Read that number before reading the options, because choosing two when it asked for three is a wrong answer that felt right.

The passing score differs by language. 48% on the English version, 55% on the Japanese. If you're reading a study group's advice, check which version they sat.

The section names are the syllabus. "Connecting to and Preparing Data" is not a category invented for the score report. It's a list of the things you'll be asked about, and the full exam guide breaks each section into numbered objectives. Reading those objective lists is the cheapest hour of preparation available.

Certification maintenance is published separately and it changes. The exam guide itself doesn't state an expiry date for this credential, and Salesforce keeps maintenance requirements on their own help pages, which were revised for the Winter '26 release. Do not assume a credential lasts forever because a blog said so. Check the maintenance page after you pass, and diarise it.

Why studying to the weights works

Splitting your hours by the published weights is not just tidy. It's the only way to make an hour of study comparable to another hour, because an exam that weights its sections unequally is telling you the exchange rate in advance. Most people never look it up, and then spread their time evenly, which quietly overpays for the smallest section.

Two findings from the memory research change how the hours themselves should be spent, and both are about how you practice rather than what you read.

The first is that testing yourself beats re-reading. Roediger and Karpicke had students study passages and then either restudy them or take a test on them. On a retention test a week later, the students who had been tested remembered substantially more than the students who had restudied, even though restudying felt more productive at the time (Roediger & Karpicke, 2006, Psychological Science , 17(3), 249–255). For a multiple-choice exam that is close to a free result: practice questions are both the preparation and the format.

The second is that the same total hours produce more if you spread them out. Cepeda and colleagues reviewed 254 studies covering more than 14,000 participants and found spaced practice reliably beat massed practice on later recall (Cepeda, Pashler, Vul, Wixted, & Rohrer, 2006, Psychological Bulletin , 132(3), 354–380). Six hours across six evenings beats six hours on a Sunday, at no extra cost.

Put those together with the weights and you get the whole method. Split the hours by section weight, spread them across days, and spend them answering questions rather than reading about answers.

On the exam facts themselves, Salesforce's published exam guide is the authority and it is the only source used above. Where a course description, a video or a summary disagrees with it, that page wins. This matters more than usual here, because the most commonly repeated third-party figures for this exam are wrong on the question count, the time limit and the passing score at the same time.

Building your own study order

Think about the last certification or test you prepared for. Did you know the section weights before you started, or did you find them afterwards on the score report?

Do this in order. It takes about twenty minutes and it's the only planning this exam needs.

Read the four section names and their numbered objectives in the Salesforce exam guide. That list is the syllabus, and reading it is faster than any summary of it.
Split your remaining hours 23 / 37 / 25 / 15. If you have twenty hours left, that's about 5, 7, 5 and 3. Write those four numbers down where you'll see them.
Spend the first block on section 2 , the exploring and analyzing work. It's the biggest section and it's also the part that makes the tool useful after the exam, so none of that time is exam-only.
Answer questions rather than read notes , from the first evening. Getting one wrong early is the cheapest way to find out what you don't know.
Spread the blocks across days, not into one weekend. Same hours, more retained, and it costs nothing to schedule.
Book the exam before you feel ready. 48% and a free retake is a low-risk first attempt, and a booked date is what turns a study plan into study.

If you have paper nearby, there's one sketch worth five minutes and it's optional. Draw the four sections as four bars at their real weights, then draw your last week of study as four bars beside them. Whichever pair is furthest apart is your next study session.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Question	Answer
Scored questions	40
Unscored questions	Up to 5, mixed in, unmarked
Time	70 minutes
Seconds per question	93 at 45 questions, 105 at 40
Passing score	48% English, 55% Japanese
Questions needed to pass	20 of 40
Questions you can miss	20
Fee	US$75, retake free
Prerequisite	None
Version tested	Tableau 2022.3
Question format	Multiple choice and multiple select. Nothing to build.
Section 1, Connecting and Preparing	23%, about 9 questions
Section 2, Exploring and Analyzing	37%, about 15 questions. The biggest.
Section 3, Sharing Insights	25%, about 10 questions
Section 4, Tableau Concepts	15%, about 6 questions. The smallest.
Former name	Tableau Desktop Specialist
Penalty for a wrong answer	None published. Never leave one blank.

The one habit to keep. Before preparing for any weighted exam, convert the percentages into question counts and split your hours the same way. It takes five minutes, it works on every certification you'll ever sit, and almost nobody does it.

One last thought, and I'd like other people's answers. The fact that changed my own preparation was that nothing on this exam is built, only recognized, which makes practice questions worth more than practice workbooks. What's the fact about an exam you wish you'd known before you started studying for it, instead of afterwards?

References

Salesforce. Salesforce Certified Tableau Desktop Foundations Exam Guide. Help article 005298988, help.salesforce.com. Checked 9 August 2026. The authority for every exam figure on this page.
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science , 17(3), 249–255.
Cepeda, N. J., Pashler, H., Vul, E., Wixted, J. T., & Rohrer, D. (2006). Distributed practice in verbal recall tasks: A review and quantitative synthesis. Psychological Bulletin , 132(3), 354–380.

Originally published on Analyst Prep Kit: Tableau Desktop Foundations Exam: 40 Questions, and 20 Right Passes It

If it was useful: Buy Me a Coffee.