Column aggregation

In addition to configuring the row aggregation, you can also configure the column aggregation. Like row aggregation, which only applies to sub-total and grand-total rows, column aggregation applies only to the subtotal and grand-total columns.

Let us first demonstrate these aggregations for total rows with a few examples, followed by the 'Manage Aggregation' dialog box where you can perform both row and column aggregations in tandem.

1. Column aggregation for a total cell

To apply column aggregation, click on the cell in the grand-total/subtotal column and select the aggregation type from the dropdown menu.

Click on the 'Aggregation' drop-down

The Aggregation drop-down menu gets enabled when you click on any cell that aggregates rows and/or columns.

You can see in the above example the default aggregation is the sum of column values for the 'Aqua Splash' brand.

Let us now change it to 'Maximum'.

Changing aggregation to 'Maximum'

As a result, the grand total is populated with the maximum value of all the quarters.

'Maximum' aggregation type - grand total

Note that for the same row, the sub-totals for each quarter are also aggregated in the same way to display the maximum value.

'Maximum' aggregation type - sub totals

2. Column aggregation for repetitive categories

When you have the same category repeated across all levels of the hierarchy, Lumel EPM will detect repeating categories and allow you to apply the same column aggregation method to all the levels in a single click.

Let us assume we want to change the column aggregation method for the category 'Beer' in the 'Artisan Ale' brand. Click on the grand total cell and select Aggregation. Note that only column aggregation is applicable here as it is a leaf row.

Aggregation for repetitive sub-category

Let us now change it to 'Average'.

Click 'Average (Children)'

When you click the option 'Average (Children)', a pop-up is displayed to confirm whether you want to apply the same aggregation to all the 'Beer' rows in all the regions.

Confirm to change for all the rows

Click Yes to update all 'Beer' rows with the 'Average' aggregation method.

Average aggregation applied to repeated categories

This ensures that the same aggregation is applied to all the repetitive categories/sub-categories in one go.

Click No in the above confirmation pop-up to apply the aggregation only to that specific row.

3. Combining row and column aggregations

Lumel EPM lets you seamlessly track the various aggregations applied to the rows and columns with the 'Manage Aggregation' dialog box.

In the example below, both row and column aggregations are set to 'Sum' for the 'Beer' category.

Both aggregations are 'Sum'

Both aggregations are 'Sum'

Let us now set the column aggregation to 'Maximum' while retaining the row aggregation to 'Sum'. To do this, click the required cell and select Aggregation -> Maximum.

Column aggregation is set to 'Maximum'

The column total now has the maximum value of the four quarters.

Row and Column Aggregation is set

You can track the same and also edit them with the 'Manage Aggregation' dialog box as illustrated below:

Manage Aggregation dialog box.

4. First/Last column aggregation

P&L or cash flow statements often require the values from the first or last periods as the column subtotals and grand totals. These aggregation types are applicable only at the column level.

• First aggregation type: The values from the first period are used for the column subtotals and grand totals. In the example, the Revenue from Q1 2026 is used as the subtotals at the year level. The Revenue from 2024 (the earliest year) is used for the grand total.

First aggregation

• Last aggregation type: The last period is used to populate the grand totals and subtotals. The Revenue of Q4-2026 is used to populate the yearly subtotal. The Revenue of 2026 is used to populate the grand total.

Last aggregation