DROPLEVEL

In Dimensionality & hierarchies

The DROPLEVEL function removes one or more levels from a node and aggregates the values of the remaining levels.

When a level is removed, all finer levels in the same hierarchy are also removed automatically (e.g. removing Quarter also removes Month and Day).

Use this function when you know which levels to remove from your data rather than which to keep: DROPLEVEL('Revenue', "Product").

Parameters

NodeNode referenceRequired

Input node, specified in single quotes (e.g. 'Revenue')

Level1, Level2, …Level nameRequired

Level names to remove from the output, specified in double quotes (e.g. "Product")

AggregationTypeStringOptional

Aggregation method for combining values. Default: Node’s own aggregation type (typically "SUM").

See also: Aggregation Settings for all available aggregation methods

Output shape

Dimensionality

Specified levels (and any finer levels in the same hierarchy) are removed

Values

Remaining values are aggregated

Row count

Reduced

Watch out

Examples

Input node: 'Revenue'

Remove the product dimension

To see total revenue per month regardless of product, remove the Product level:

Formula: DROPLEVEL('Revenue', "Product")

Removing a parent level cascades to finer levels

To get a single total per product across all time, remove the Quarter level. Because Month is finer than Quarter in the same hierarchy, it is also removed automatically.

Input node: 'Revenue'

Formula: DROPLEVEL('Revenue', "Quarter")

Both Quarter and Month are removed because Month is a finer level within the same hierarchy.

Using AVG instead of SUM

To find each product’s average monthly revenue instead of the total:

Formula: DROPLEVEL('Revenue', "Month", "AVG")

See also

ROLLUP

When you know which levels to keep rather than remove.

ROLLUP_TO

When you want to match another node’s dimensional structure automatically.

EXPAND

When you need to add levels instead of removing them (the inverse of DROPLEVEL).