Calculations & queries

Available now in the desktop app Calculated fields, Summary Calculations and the Query Workbench all work in the desktop app today. Snapshots over time come with the full release.

If you can write a spreadsheet formula, you can write this.

Most reports need no formula at all: pick the columns and Insight FastPass does the sums, averages and groupings. When you need more — a running total, a change on last month, a value from a linked table — one spreadsheet-style language does it, with autocomplete and a hint for every argument.

  • One formula language for columns, totals and styling rules
  • Running totals, previous-period comparisons and ranks, each in one function
  • Look up values in linked datasets
  • A SQL-style query page for analysts, on Plus and Ultimate
Typing RUNNINGTOTAL in the formula editor, with autocomplete and the parameter hint, and the result in a KPI
Demo — formulas with autocomplete and hints

One language, three places

Learn it once, use it everywhere.

The functions, the rules about blanks and the way you write a column are the same wherever a formula goes. What changes is which rows it can see.

Calculated field

A new column, worked out per row

Margin, days late, a due date from the customer’s payment terms.

DATEADD([Invoice Date], Customers[Payment Terms], "day")

Summary Calculation

One value per bar, slice or KPI

Running totals, change on the previous period and rank, worked out across the visual’s categories.

SUM([Sales]) - PREVIOUS(SUM([Sales]))

Styling rule

A TRUE or FALSE test per row

Color a cell or a row when the formula holds, as conditional formatting does in Excel.

[Promise Date] < TODAY()

The ones people ask for

The calculations that are usually hard, in one line each.

Running total

Added up in the order of the axis.

RUNNINGTOTAL(SUM([Sales]))

Change on last period

Blank for the first period.

SUM([Sales]) - PREVIOUS(SUM([Sales]))

Rank

1 for the largest.

RANK(SUM([Sales]))

From a linked table

Any column of a linked dataset.

Customers[Region]

No formula needed

The common ones are a switch, not a formula.

Show as percent of total, roll up by sum, average, count and more, add a target line, flag values above or below a threshold, keep the top 10 and fold the rest into “Other”. Number and date formats are set once on the column, and every report shows them that way.

  • Percent of total, roll up and sort on every visual
  • Target lines and flags on charts, and a goal on a KPI card: 1.2M of 1.5M
  • Clustered columns from several values, and one line per region with the rest as Other
  • Formats kept with the dataset, not the chart
  • On Ultimate, ask FastPass for the calculation in plain words and check it against the data
Demo — click a bar and the rest of the page follows

For the analyst Plus & Ultimate

Query your datasets, and keep the result as a dataset.

The Query Workbench works like the query window you already know: an Object Explorer on the left, query tabs on the right, results underneath, F5 to run. FPQL is shaped like SQL, and everything inside it is the same formula language as the reports.

Save the result as a dataset

Reports use it like any other dataset, and it re-runs by hand or on a schedule — so the cleanup you wrote once runs again on its own.

Joins, temp tables and parameters

Combine datasets, keep steps in temp tables, and on Ultimate save replay queries that take parameters.

SELECT o.JobID, c.CustomerName
FROM #Open AS o
JOIN Customers AS c ON c.CustomerID = o.CustomerID

Change rows in bulk

UPDATE, INSERT and DELETE on a dataset, for the people allowed to change it.

The FPQL reference

The rest of the query window

An execution plan and index advice for a slow query, saved queries shared with the workspace, tabs that are still there tomorrow, and the keyboard shortcuts you already use, column selection included.

The Query Workbench, with the Object Explorer on the left, an FPQL query run with F5 and the results underneath
Demo — query your datasets, save the result as a dataset

Over time Ultimate

Keep every export, and trend it.

Many systems export a list as it stands today: open orders, a backlog, balances. Keep each export as a dated snapshot in one dataset, and a chart shows how it moved — no history tab to maintain.

  • Add a snapshot by upload, or on every database refresh
  • Thin old snapshots with a rule: keep every one for 3 months, then one a month
  • Load just the last 12 weeks instead of every row since 2019
Demo — every export kept as a snapshot, trended over time

Styling

Late rows in amber. Top deals flagged.

Add conditional styling to any grid with a condition, a formula or a colour scale from low to high. Each grid keeps its own rules, every change can be undone, and nested grids get rules of their own. Heat maps, calendars and region maps take a colour scale too.

The sample grid with orders over 2,000 shaded amber and the five largest customers’ cells green, beside the rules that do it
Demo — conditional styling on a grid

Questions about calculations

Do I have to write formulas?

No. Totals, averages, counts, percent of total and top-N are settings on the visual. Formulas are there for what those don’t cover.

Which plans have formulas?

All of them, Free included. The Query Workbench is on Plus and Ultimate, for editors and up; snapshots are on Ultimate.

Can I reuse a calculation in another report?

Yes. Import a Summary Calculation from another report in the workspace, and a calculated field lives on the dataset, so every report on it has the column.

I know SQL. Will FPQL feel familiar?

It should: SELECT, FROM, WHERE, GROUP BY, ORDER BY, joins, subqueries and temp tables. The guide has a “Coming from T-SQL” section for the differences.

Have a report that needs one tricky number? Bring the spreadsheet, or the database behind it, and build it here.

The Dasher: a CSV in a hurry to become a dashboard.
Try it free before you sign up