Best Practices for Handling Aggregated Expressions in Bold BI
Handling Aggregated Expressions in Bold BI
When using aggregations in Bold BI, such as the Average function, it’s essential to understand how grouping may affect the calculation of the resulting data. This article will explain how to create an expression that produces the expected output, factoring in grouping and the behavior of the AVG function.
Aggregations and Grouping in Bold BI
When working with visualized data in Bold BI, it’s common for the table to be grouped by a specific column. When using the AVG function in an expression, it will calculate the average separately for each group.
Consider the below sample table below, which has score, min_rating_scale, max_rating_scale, and user_id columns.
If you have created an expression that includes an aggregation (i.e., Average), such as the one below:
The expression calculates the average score for each user based on the raw scores, minimum rating scale, and maximum rating scale.
The resulting widget may not meet your expectations, as it will show the average for each group separately. To obtain a single value for the entire column, you should use the TOTAL function with the AVG function.
Creating an Expression for the Desired Output
To achieve a single value for the whole column, create an expression using the following:
The expression will generate the expected average of the whole column, taking into account the desired aggregation.
Important Note: The conditional formatting values displayed in the data preview section are based on raw data. The aggregated data can be viewed in the widgets.
Related Articles:
