Multipass Calculations

A multipass calculation is a two-level aggregation, written as AGG(slicer, inner_measure). The inner measure is first calculated with an additional slicer, then the outer aggregation is applied over those results without slicing by that slicer. The levels are calculated from the inside outwards.

For example, AVG([Day], SUM([Revenue])) first calculates SUM([Revenue]) sliced by Day (inner level), then averages those daily sums (outer level) without slicing by Day.

Terms and Definitions

  • measured value - Often formulas must take into account specific criteria. To do this, Sisense provides a feature called Measured Value, which like the SUMIF function in Excel, only performs a calculation when the values meet a set of criteria. Criteria for measured values may be based on any logical operators in a filter. For more information, see Measured Values.

  • slicer - A slicer is a dimension that is used to divide the results into groups.

  • filter - A filter restricts the result set to those that meet the filtering criteria. For more information as to how filters affect results, see Measured Values.

  • inner measure - A simple measure that is calculated first, sliced by the multipass slicer. For example, in AVG([Day], SUM([Revenue])), SUM([Revenue]) is the inner measure.

  • outer measure - The aggregation applied over the inner measure's result set, without slicing by the multipass slicer. In the example above, AVG is the outer measure.

Multipass Calculation Behavior

For multipass calculations that have a filter on a dimension and measured value calculations with a filter on a dimension, the filter on the dimension is applied for the calculation and, for presentation, the filter on the widget or dashboard is applied.

Example:

AVG (X, SUM (Y))

  • X (first parameter) is the multipass slicer dimension

  • SUM (Y) (second parameter) is the inner measure

  • AVG is the outer aggregation, applied over the inner measure's result set without slicing by X

When the Multipass Slicer is also a Widget Slicer (Rows/Columns)

If the multipass slicer dimension is also placed in the widget's Rows or Columns panel, the outer aggregation's calculation will not slice by that dimension. The result is a single value displayed uniformly across all rows of that dimension in the widget.

Example:

A Pivot table with [Brand] in the Rows panel and the formula AVG([Brand], SUM([Cost]))

  • Inner level: SUM([Cost]) is calculated per Brand

  • Outer level: AVG aggregates across all Brands, producing a single value, shown on every Brand row