DROPLEVEL

Category: Dimensionality & hierarchies

Overview

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.

Syntax

DROPLEVEL('Node', "Level1" [, "Level2", ...] [, "AggregationType"])

Example usage: DROPLEVEL('Revenue', "Product")

Parameters

Parameter	Description	Type	Required	Default
Node	Input node, specified in single quotes (e.g. `'Revenue'`)	Node reference	Yes	—
Level1, Level2, ...	Level names to remove from the output, specified in double quotes (e.g. `"Product"`)	Level name	Yes	—
AggregationType	Aggregation method for combining values	String	No	Node's own aggregation type (typically `"SUM"`)

See also: Aggregation Settings for all available aggregation methods

Output Shape

Aspect	Behavior
Dimensionality	Specified levels (and any finer levels in the same hierarchy) are removed
Values	Remaining values are aggregated
Row count	Reduced

Watch Out

Removing a level also removes all finer levels below it in the hierarchy.
DROPLEVEL specifies what to remove. If you want to specify what to keep, use ROLLUP instead.
The default aggregation is the node's own aggregation type (typically SUM). Use the AggregationType parameter when you need a different method.

Examples

Input node: Revenue

Month	Product	Value
2025-01	Alpha	50
2025-02	Alpha	45
2025-01	Beta	40
2025-02	Beta	54

Remove the product dimension

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

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

Month	→ DROPLEVEL Result
2025-01	50 + 40 = 90
2025-02	45 + 54 = 99

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

Quarter	Month	Product	Value
Q1	2025-01	Alpha	50
Q1	2025-02	Alpha	45
Q1	2025-03	Alpha	60
Q1	2025-01	Beta	40
Q1	2025-02	Beta	54
Q1	2025-03	Beta	35

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

Product	→ DROPLEVEL Result
Alpha	50 + 45 + 60 = 155
Beta	40 + 54 + 35 = 129

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

Product	→ DROPLEVEL Result
Alpha	(50 + 45) / 2 = 47.5
Beta	(40 + 54) / 2 = 47

Related Functions

Function	When to use instead
ROLLUP	Use ROLLUP when you know which levels to keep rather than which to remove.
ROLLUP_TO	Use ROLLUP_TO when you want to match another node's dimensional structure automatically.
EXPAND	The inverse operation. Adds levels by combining every existing row with every member of the new level.