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 roll monthly revenue up to a single figure per product and year, 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. Year stays: DROPLEVEL rolls the values up to the level directly above the coarsest level you removed, so the result is one figure per product per year, not one across all time. To lose the time dimension altogether, remove "Year" instead, the top level of the hierarchy.

Using AVG instead of SUM

To find each product’s average monthly revenue instead of the total, remove the Month level. Quarter sits directly above Month, so the result is one average per product and quarter.

Input node: 'Revenue'

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

Related

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).