Level 2 · The grain of a table

Linda’s two numbers come from the same data. The only difference is the grain of the table the fee was summed in.

What this practice level is

This is a quiz in a practice tool built for this site; Power BI Desktop itself runs only on Windows. The numbers are real: they are what a measure returns on Harbor & Pine’s data. In Power BI Desktop you would see each table’s rows in Table view (the second icon on the left rail), which is the quickest place to check what one row means.

Grain: what one row stands for

The grain (or granularity) of a table is the level of detail of one row:

Table	Grain
order_items	one order line
orders	one order
customers	one customer
Date	one day
a targets sheet	one category per month

Grain is not about size. It is the answer to “if I count the rows, what am I counting?”

Numbers belong to a grain

Each number is measured at one grain. quantity and line_total belong to the order line. shipping_fee belongs to the order: a customer pays shipping once, however many products are in the box. A monthly sales target belongs to a month, not to a day.

Put a number in a table with a finer grain and it gets copied onto many rows. Sum it and it is counted once per copy. On Harbor & Pine’s data:

Shipping (orders)     = SUM(orders[shipping_fee])                      -- $32,016.06
Shipping (flat table) = SUMX(order_items, RELATED(orders[shipping_fee])) -- $58,782.63

The second formula reproduces the merged table: it reads each line’s order fee and adds them up. Orders with several lines count their fee several times, which is why it is almost double.

Keep grains apart, then relate them

The fix is not a clever measure. It is the model: keep orders at order grain and order_items at line grain, and relate them on order_id. A measure on fees sums orders; a measure on units sums order_items; a slicer on a dimension filters both through the relationships. The next levels build exactly that.

When grains really differ

Sometimes the coarser data is all you have, like targets set per category per month. Don’t force it down to days or lines. Load it as its own table at its own grain and compare it with actuals at that grain, in a visual by category and month.

Common mistakes

• Flattening for convenience. One wide table feels simpler and quietly multiplies every order-level number.
• Averaging repeated values. AVERAGE of the repeated fee is wrong too: orders with more lines weigh more.
• Not stating the grain. If nobody can say what a row means, nobody can say what COUNTROWS counts.