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. | Node reference | Yes | — |
Level1, Level2, ... | Level names to remove from the output | Level name | Yes | — |
AggregationType | Aggregation method for combining values | String | No |
|
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 |
Removed levels | All other levels 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 SUM. Use the AggregationType parameter when you need AVG or another 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
Aggregate revenue by month, regardless of product.
Formula
DROPLEVEL('Revenue', "Product")
Month | → DROPLEVEL Result |
|---|---|
2025-01 | 50 + 40 = 90 |
2025-02 | 45 + 54 = 99 |
Remove the time dimension
Aggregate revenue by product, regardless of month.
Formula
DROPLEVEL('Revenue', "Month")
Product | → DROPLEVEL Result |
|---|---|
Alpha | 50 + 45 = 95 |
Beta | 40 + 54 = 94 |
Using AVG instead of SUM
Calculate the average monthly revenue per product instead of the total.
Formula
DROPLEVEL('Revenue', "Month", "AVG")
Product | → DROPLEVEL Result |
|---|---|
Alpha | (50 + 45) / 2 = 47.5 |
Beta | (40 + 54) / 2 = 47 |