A rollup column aggregates values from related records in a linked table and displays the aggregated result in the current table. Use rollup columns to calculate values such as sum, count, average, minimum, maximum, median, earliest date, latest date, countA, empty, and more.
Show the number of open tasks assigned to an employee.
Calculate the total value or quantity of orders for a product or customer.
Display the latest or earliest order date for a product.
A rollup column is similar to a reference column but also automatically calculates aggregated values from related records. This eliminates the need to manually maintain summary values across multiple tables. When data in the linked table changes, the rollup value is recalculated automatically.
This article explains how to create and configure a rollup column by using a sample Products table.
In this example, you create:
A rollup column that displays the total order quantity for each product.
A rollup column that displays the latest order date for each product.
Go to PowerTable > Insert Column > Visual Column > Add Roll Up Column. A side panel opens.
Configure the following details:
Column Name: Enter an appropriate name for the rollup column, such as Total Orders.
Schema: Select the schema that contains the linked table.
Linking Table: Select the table that contains the records to aggregate.
Column from Current Table: Select the column in the current table used to match records.
Column from Linking Table: Select the matching column in the linked table. In this example, ProductKey is the matching column.
Column To Rollup: Select the column to aggregate. Here, choose OrderQuantity as the column to roll up.
Aggregation: Select an aggregation function, such as Sum, Average, or Count. Use Sum to add and aggregate the order quantities for each product.
If Condition: Optionally, add one or more filter conditions by using Add Filter to aggregate only the records that meet specific criteria. Combine multiple conditions by using AND or OR. For example, you can aggregate only the orders placed after a specific date by filtering on the OrderDate column.
The display properties for the rollup column can be configured in the panel's Display tab. To learn more, see Display.
Select Save.
A rollup column that displays the aggregated order quantity for each product is added based on the matching product key.
Products with no related records or no values to aggregate display a blank value in the rollup column.
To create another rollup column that displays the latest order date, repeat the same steps. Select Order Date as the column to roll up, and select Latest Date as the Aggregation type. Select Save.
The latest order date rollup column is added, as shown in the following image.
Enterprise planning, Integrated with PowerTable and Intelligence, native to Microsoft Fabric. Co Engineered with Lumel.
