Floemdata
BI & Reporting

Defining Measures and Formulas in BI Dashboards

A BI measure should be defined once, in one place: sums, ratios, time dimensions and filters. We explain the catalog approach and traps such as working days.

Floemdata · · 3 min read
Dashboard with three cards: a gauge, a line chart and a bar chart, with no numbers

When two people say 'this month's sales' and give different numbers, the problem is usually in the definition, not the data: one subtracted the discount and the other did not; one counted cancellations. In a BI dashboard, a measure should be defined once and in one place.

Measure, dimension and derived measure

  • Measure: the number that is added up. Examples: net sales (sum), number of documents (distinct count), number of customers (distinct count).
  • Dimension: the axis along which you break the number down. Examples: month, product group, region, sales rep.
  • Derived measure (ratio): dividing two measures after they are summed. Example: margin percentage = gross profit ÷ net sales × 100.

The time dimension and hierarchy

Time is a natural hierarchy: year, quarter, month, day. Showing a measure at year level and at month level with the same definition keeps the total consistent when you move up a level. In the dimension catalog of Floemdata Analytics, year, quarter, month and date exist as separate dimensions.

Three traps

  • Averaging a ratio. The average of monthly margin percentages is not the annual margin; sum gross profit and net sales first, then divide.
  • Month length. February and a month with a public holiday differ in the number of working days; comparing monthly totals can show a fake decline. A value per working day gives a fairer comparison.
  • Two definitions in two places. If the dashboard uses one formula and Excel another, you get two numbers.

How Floemdata does it

In Floemdata Analytics, reports are produced from a catalog of measures and dimensions; if a measure outside the catalog, a raw column or an unknown field is requested, the system raises an error. The reports go through a single compiler, so the same measure is calculated with the same definition in every report; the dashboard's company summary view is computed outside that path with its own query, and the equality of the two paths has not been measured (not verified). The catalog includes derived measures such as net sales per working day and margin percentage. We do not claim a separate 'hierarchy object'; the time dimensions stand as separate fields.

To get started, write down the three most disputed measures and give each a one-sentence definition. Questions: info@floemdata.com.

← All guides