Appearance
Formulas
Formulas add calculated data to a data source without touching your lists. This section explains them with a working demo you can import and play with: start with the demo app, follow the walkthrough, or keep the cheat sheet at hand.
There are three kinds:
- Dynamic columns: a formula calculated for every row, for example Cost = Hours x Rate. It behaves like any other column in grids, charts and filters.
- Aggregated fields: one value calculated over all rows, for example the total budget or the latest invoice date. Good for summary cards, gauges and thresholds.
- Reference joins: make a whole second data source available inside your formulas, then look up, filter and total it per row.
Who this is for
- Data source and dashboard designers. You don't need to be a developer. Every formula here can be copied, and the snippets in the editor give you a starting point.
- Advanced users and developers. The "For developers" boxes explain what happens under the hood, along with the performance trade-offs.
New to formulas?
A formula works like a spreadsheet formula with a different notation: you write {[Hours]} * {[Rate]} instead of =B2*C2. You don't need to know JavaScript to follow this guide. Formula basics for spreadsheet users translates the notation on one page.
Which one do I need?
| I want to... | Use | Example from the demo |
|---|---|---|
| Show totals per group (per project, per month) with no formula | A Pivot grid, or a Grid grouped with Show Group Totals | Total cost per project from a Costs list |
| Calculate something for each row from that row's own values | Dynamic column | Cost = {[Hours]} * {[Rate]} |
| Calculate one number for the whole data source | Aggregated field | Portfolio budget = Sum({[Budget]}) |
| Compare each row with a total, e.g. "share of all spend" | Late dynamic column | {[Total spent]} / {[Portfolio spent]} |
| Bring in rows from another list that don't match 1:1 | Reference only join | All tasks of a project, all invoices of a project |
| Total or look up those other rows per row, quickly | The Rows helper | Total hours of each project's tasks |