Repository navigation
Column selection
Open a report's options menu and choose Columns to configure the fields used by both its chart and table. The dialog contains Columns, Calculated measures, Data preview, and Visualizations sections. Open documentation links to this page.
This page describes the expanded column dialog in the upcoming release. Older releases provide dimension selection and the aggregation switch; shared calculated measures, per-measure aggregation, sorting priorities, and the data preview require the expanded dialog.
Each source column has an Include checkbox. Its effect depends on the column's role:
- A dimension describes a group, such as Region, Product, or Date. With Aggregate values enabled, included dimensions define the groups. Removing a dimension combines rows that have the same remaining dimensions.
- A measure is a value, such as Revenue or Quantity. Clearing Include hides it from the shared report output. It remains available as an input to calculated measures.
For example, a report with Region, Product, and Revenue normally groups revenue by each Region/Product combination. Exclude Product to get revenue by Region. Exclude all dimensions to get an overall result when aggregation is enabled.
Keep at least one output column included. To hide or reorder columns only in the rendered table, use Table options instead.
The Aggregate values switch in Visualizations is enabled by default. It combines rows with the same included dimensions. Disable it to keep individual source rows; excluded dimensions still disappear from the output, but their rows are not combined.
Choose an Aggregate method for each source measure:
| Method | Result within each group |
|---|---|
| Sum | Sum of numeric values. |
| Average | Average of numeric values. |
| Minimum | Smallest value. |
| Maximum | Largest value. |
| Count | Number of nonempty values. |
| Count distinct | Number of distinct nonempty values. |
Count and Count distinct ignore missing and empty values. With aggregation disabled, these methods do not combine source rows.
Choose Ascending or Descending beside a source column or calculated measure. Choose None for a source column, or No sort for a calculated measure, to remove its sort rule.
When two or more columns are sorted, Priority fields appear. Give every sorted column a different positive whole number. Lower numbers are evaluated first; later rules break ties. A single sorted column does not need a priority.
For example, sort Region ascending with priority 1 and Revenue descending with priority 2 to list regions alphabetically and show their highest-revenue rows first. An excluded dimension cannot sort aggregated rows; hidden measures can still be used for sorting.
Select Add calculated measure, enter a unique Measure name, and build a Formula. Click a column button to insert it at the cursor, or drag it into the formula. Inserted columns normally appear as [Revenue] or [Quantity]; buttons disambiguate source columns with identical names.
Formulas support numeric constants, +, -, *, /, parentheses, and negative values. For example:
[Revenue] - [Cost]
([Revenue] - [Cost]) / [Revenue] * 100
Shared measure formulas also support assigning numeric weights to values with LOOKUP(); see Weighting. Functions such as ROUND() are available in table-specific formulas, but cannot be used here. Missing or nonnumeric inputs and division by zero produce blank results.
Use Calculation timing to choose when the formula runs:
| Timing | Use it for |
|---|---|
| For each source row | Calculate a value for every original row, then aggregate those values using the selected method. |
| After aggregation | Calculate from the already aggregated measure values of each group. This is the default. |
Suppose two source rows in the same group have Revenue/Quantity values of 100/1 and 100/9.
-
For each source row, with
[Revenue] / [Quantity]and Average, averages100and11.11…, giving approximately55.56. -
After aggregation, with Revenue and Quantity both set to Sum, divides
200by10, giving20.
Use the second option when you want the group's total revenue divided by its total quantity. Use the first when you want the average of individual row ratios. With Aggregate values disabled, both timings operate on individual rows.
Calculated measures can reference other named calculated measures. A calculation performed for each source row can only depend on other calculations with that timing. Circular references are invalid. An after-aggregation formula cannot reference an excluded dimension when aggregation is enabled.
Each calculated measure also has Include, sorting, and Remove controls. Excluding it hides its output while keeping it available to other formulas. Removing it deletes the calculation from the draft; update any formulas that reference it before applying.
Assign weights when different categories should contribute differently to a result, such as sales weighted by customer segment or points awarded by article tier. This feature is available in the upcoming release.
-
Select Add calculated measure and name it Weight.
-
Enter a formula that assigns a number to each category. Insert your source column using its column button:
LOOKUP([Segment], 'Government', 3, 'Enterprise', 2, 'Channel Partners', 1, 1)This assigns Government a weight of 3, Enterprise 2, and Channel Partners 1. The final number is the fallback for any other or missing value.
-
Choose For each source row. To use the weight in another calculation without summing the weights themselves, choose None (original values) as its aggregation.
-
Add another measure named Weighted sales, with formula
[€] * [Weight](replace[€]with your sales column). Choose For each source row and Sum. -
Preview the result, select OK, and save the report.
| Segment | Sales | Weight | Weighted sales |
|---|---|---|---|
| Government | 269,035 | 3 | 807,105 |
| Enterprise | 400,156 | 2 | 800,312 |
| Channel Partners | 221,976 | 1 | 221,976 |
None (original values) preserves weights as grouping values. If you want one total across categories with different weights, set the Weight aggregation to Sum and clear its Include checkbox, then exclude the category dimension. Weighted sales is still calculated from each original row before being summed.
For article scoring, use a single measure named Points:
LOOKUP([Tier], 'T1', 3, 'T2', 2, 'T3', 1, 0)
Choose For each source row and Sum to total the points. Exclude Tier and any article-specific dimensions when you want a combined total.
The syntax is LOOKUP(column, value, weight, ..., fallback). Supply one or more value/weight pairs and a final fallback.
- Text values go in single quotes. Matching is exact and case-sensitive, including spaces.
- Write an apostrophe as two single quotes, for example
'Editor''s choice'. - The first matching pair wins. Unmatched or missing values use the fallback.
- Weights and the fallback can be numbers or numeric expressions.
- Column names in square brackets must refer to your report's actual columns.
- Disable Top N and Time aggregation before using calculated measures.
If compatible calculations already exist in Table options, the dialog offers Move table calculations to report. This imports them into the draft so they can supply both the chart and table. Select OK to apply the draft and complete the move.
Pivot, threshold-based, and unsupported table calculations remain in Table options. Moving a compatible calculation changes it from a table-layout calculation to a shared report measure; check its result with the preview, especially when inputs contain text or missing values.
Select Preview result in Data preview to fetch the draft result without changing the report. The preview shows up to ten result rows and uses the current filters. Save a new report before requesting a preview.
OK applies the draft in the current report and closes the dialog. Use the report save action to persist the configuration. Cancel discards the dialog draft, including calculation imports; a preview does not save changes.
Remove Top N and Time aggregation before adding shared calculated measures, hiding source measures, changing source aggregation methods, or using multiple sort rules. The dialog reports an error when these settings are combined.
Calculations require complete source data. A source that has already paginated its result cannot supply shared calculations. A source that already aggregates its data may also prevent row calculations or regrouping; use a source that supplies the required original rows.
Use Chart options for chart presentation and Table options for table layout and formatting. Column selection controls the shared report data supplied to both.
