How to Calculate Row-Wise Column Totals and Display Them in a New Grid Column in Bold BI
This article explains how to calculate a row-wise total across multiple columns in Bold BI and display the result as a new column in a Grid widget. It also shows how to enable a summary row to display the overall total at the bottom of the grid.
This approach is useful when each record must show a combined value, while the grid also needs to present an aggregated total for all displayed rows.
Step 1: Create a Calculated Field for the Row‑Wise Total
- To calculate a row‑wise total, you must define a calculated field that sums the required columns for each record.
- Open your dashboard in Edit mode and click on the Data Source edit icon to open the Data Designer.
- Click the Expression icon to create a new calculated field.
- Enter a row‑wise expression that sums the required columns while handling null values.
Example calculated field expression using CASE logic:
CASE WHEN [Col1] IS NULL THEN 0 ELSE [Col1] END
+ CASE WHEN [Col2] IS NULL THEN 0 ELSE [Col2] END
+ CASE WHEN [Col3] IS NULL THEN 0 ELSE [Col3] END
+ CASE WHEN [Col4] IS NULL THEN 0 ELSE [Col4] END
+ CASE WHEN [Col5] IS NULL THEN 0 ELSE [Col5] END
- Provide a meaningful name for the field, such as Total.
- Save the expression and apply the changes to the data source
You can simplify null handling using functions like COALESCE or ISNULL, which replace null values with 0 before performing the calculation. The COALESCE function returns the first non-null value in the expression, making it a cleaner and more concise alternative to multiple CASE WHEN checks.
Example:
COALESCE([Col1], 0) +
COALESCE([Col2], 0) +
COALESCE([Col3], 0)
Step 2: Add the Calculated Field to the Grid Widget
- Select or add a Grid widget to the dashboard.
- Click on the Settings (gear) icon to open the Assign Data panel for the Grid.
- If you added a new Grid widget, assign the required fields (for example, Category, Col1–Col5).
- Drag the newly created Total field into the Grid columns list.
- (Optional) Reorder the columns if required.
The Total column will now show the row‑wise sum for each record.
Step 3: Enable the Summary Row in the Grid
-
To display an overall summary at the bottom of the Grid:
- Select the Grid widget.
- Open the Properties panel.
- Enable the Summary Row option.
Note: Summary row aggregations work best on numeric fields, as functions like Sum and Avg require numeric data types to produce meaningful results.
Step 4: Configure Summary Types
- To display a label such as Total in the summary row:
- Select the Category column from the left pane.
- Click Add Summary.
- Set the following options:
- Type: Expression
- Title: Total
- Show Value: Enabled
- Optionally configure Title and value colors:
- Optionally choose your preferred background color from the Summary Row settings:
- Apply the changes.
- This adds a static “Total” label to the first column of the summary row, making the grid output easier to understand.
Output
-
Click on the Preview Button to verify the Grid displays:
- A Total column showing row‑wise sums.
- A Summary Row at the bottom with:
- A “Total” label
- The overall sum of all row totals
-
This provides both detailed and aggregated insights in a single view.
Conclusion.
By using a calculated field along with the Grid widget’s summary row, you can display both row-level totals and overall aggregates in a single view. This approach improves clarity and is especially useful for financial and operational reporting scenarios.
Related Articles:
