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