Choose the Reporting Grain Before You Choose the Chart

Scott — Thu, 30 Jul 2026 20:17:28 +0000

A chart can be perfectly formatted and still be wrong. In accounting and operational reporting, the most common cause is not the visual—it is the grain of the data underneath it.

Grain means what one row represents. One row might be an invoice, an invoice line, a payment, an account balance at a reporting date, a CRM deal, or a single activity. Those are different business events.

When they are treated as interchangeable, totals multiply, dates lose their meaning, and filters answer a different question from the one in the chart title.

Start with the business question

Before choosing a table or chart, write the question in one sentence:

How much is outstanding by customer? This is an invoice-level question.
Which products generated net sales? This is an invoice-line question.
When did cash arrive? This is a payment question.
What was the account balance at month end? This is a financial-summary question.

The chart type comes later. A bar chart cannot repair a table that mixes recognised revenue, invoice value, and settlement movement.

Four accounting grains that should stay distinct

Invoice summary

One row per invoice or bill. Use it for document totals, due dates, paid amounts, balances, and customer or supplier analysis.

Invoice lines

One row per product or service line. Use it for item, tracking, quantity, net, tax, and gross analysis.

Payments

One row per authorised settlement. Use it for cash timing, receipts, supplier payments, and payment accounts.

Financial summary

One row per account and reporting period or date. Use it for profit-and-loss, balance-sheet, and account-movement reporting.

The same organisation can have valid numbers at every one of these grains. The mistake is expecting them to reconcile by simple addition. An invoice amount is not recognised revenue, a payment is not an invoice total, and an invoice line cannot safely carry the full invoice value on every row.

Why totals multiply

Suppose one invoice has five lines and a gross value of £1,000. If the invoice total is joined onto every line and then summed, the result is £5,000.

Nothing is wrong with the underlying records. The model simply repeated an invoice-level measure at a line-level grain.

The same effect appears in CRM reporting. One deal joined to eight activities produces eight rows. Summing the deal amount across those rows multiplies pipeline value by eight.

Distinct counts do not solve every version of this problem. A distinct count can correctly count invoices or deals, but it does not make a repeated amount safe to sum. A distinct sum is also unreliable because two legitimate records can have the same value.

A safer star-schema pattern

Keep fact tables at their natural grain. Invoice facts, invoice-line facts, payment facts, and financial-summary facts can share dimensions such as date, contact, account, or tracking category, but they do not need to be flattened into one universal fact table.

Use stable business keys and surrogate keys consistently.
Create role-playing date dimensions when invoice date, due date, and payment date answer different questions.
Keep measures on the fact table where they are naturally additive.
Document non-additive balances and snapshot measures.
Build relationships first, then create measures, then choose visuals.

Refreshes need a stable contract

A good model is not only correct once. It should also be refreshable.

Stable file names, consistent columns, explicit types, clear endpoint results, and a known reporting grain make the refresh predictable. If a source adds data or an endpoint returns a partial result, the run evidence should make that visible before the dashboard is trusted.

This is why the data-preparation layer matters. It separates source retrieval from the reporting model and gives the analyst a place to validate row counts, dates, totals, and relationships.

A practical pre-chart checklist

Before building a visual, ask:

What does one row represent?
Which date gives the measure its business meaning?
Is the measure additive across rows, time, and categories?
Can a join repeat the measure?
Which table owns the measure?
What run evidence confirms that the refresh completed as expected?

Where Datplan DataPull fits

Datplan DataPull uses separate documented reporting grains for authorised business data and prepares local fact-and-dimension exports for BI tools.

The product does not remove the need to understand the model. It makes the grain and refresh boundary easier to inspect before a polished chart repeats the wrong value.

For a practical Xero example, see How to prepare a Xero star schema for Power BI.

DEV Community: Scott