Calculations, Parameters and Stories

Calculated fields

Choose New calculated field in the field list. Give the field a name and an expression. The expression is evaluated for each row of the dataset, and the result is saved as a new dimension or measure (the Dashboard infers which, and you can override it). A calculated field belongs to the dataset it was written against.

Syntax

• Fields are written in square brackets: [Unit Price]. A name without spaces or keywords can be written bare.
• Literals: numbers (10, .5), strings in single or double quotes, true and false.
• Arithmetic: + - * / % and parentheses.
• Comparison and logic: = or ==, != or <>, <, <=, >, >=, and and, or, not (or && and ||).
• Conditions: IF ... THEN ... ELSEIF ... THEN ... ELSE ... END.

Functions

ABS(x), ROUND(x, digits), SQRT(x), LEN(text), UPPER(text), LOWER(text), CONTAINS(text, part) (not case-sensitive), ISNULL(x), IFNULL(x, fallback), MIN(a, b, ...) and MAX(a, b, ...).

Examples

[Revenue] - [Cost]

IF [Score] >= 90 THEN "High" ELSEIF [Score] >= 60 THEN "Medium" ELSE "Low" END

ROUND([Profit] / [Revenue] * 100, 1)

The editor reports problems such as a missing bracket as you type. Because the expression runs per row, aggregate afterwards: put the calculated measure on a shelf and pick sum, average or another aggregation.

Parameters

A parameter is a named value a reader can change without editing the chart. Create one from the Parameters pane as either:

• Number range: a minimum, maximum and step, with a slider.
• List: a fixed set of values, chosen from a dropdown.

Use a parameter in two ways:

• In text: write {name} in a chart title or axis title, such as Sales above {threshold}; it is replaced with the current value.
• To swap a field: bind a list parameter to a shelf slot so the reader chooses which column drives the chart. A parameter called Measure that lists Sales, Profit and Quantity lets one chart show any of the three.

Reference lines and bands

Add a line at a fixed value, or at the average, median, minimum or maximum of a measure, with a label template where {value} is replaced by the resolved number. A band shades the region between two such values, for example between the average and a target. Reference marks are available on cartesian charts.

Trend lines and forecasts

Under Trend / Forecast a chart can show:

• A trend line: linear, polynomial (degree 2 or 3), exponential or logarithmic
• A moving average
• A forecast: linear, exponential smoothing or moving average, drawn with a confidence band

These are simple fits computed from the aggregated data on the chart. Treat a forecast as an illustration, not a prediction you can rely on. Histograms can overlay a normal (Gaussian) curve, and scatter charts can show cluster centroids.

Annotations

An annotation pins a marker and a short callout to one data point, such as "highest sales month". It points at a category value and a measure, and can take a colour.

Bookmarks and stories

A bookmark saves the dashboard's current filters, cross-filter and slicer state under a name. A story is an ordered list of bookmarks, each with a caption. Add steps from your bookmarks, reorder them, write a caption for each, and press Play story to walk a viewer through the result one step at a time. Stories are replays of bookmarks, not separate copies, so editing the dashboard later does not leave the story behind.

Putting it together

1. Build charts and add the filters and slicers a reader will need.
2. Save a bookmark for each view worth showing.
3. Make a story from those bookmarks and caption each step.
4. Pin the dataset version, or let a pipeline refresh the data on a schedule.

See Building charts and dashboards for the chart types and filters.