How to Use Conditional Formatting to Visualize Data Based on Average Value in Another Column

In Bold BI, you can apply conditional formatting to a grid widget to visually differentiate rows based on how their values compare to the average of another column. This article will guide you through the process of setting up conditional formatting to display text indicators such as “LOW” or “HIGH” when a row’s value is lower or higher than the column’s average. To accomplish this, follow the steps below:

Calculate the Overall Column Average

First, you need to create an expression to calculate the overall average of the column you’re interested in. Use the following expression format and replace [Column_Name] with the actual field name you want to calculate the average for:

Overall Column's Average Expression: TOTAL(AVG([Column Name]))

Compare Value with Overall Average

Next, create another expression to compare the specific value with the overall column’s average. Configure the expression created in Step 1 within the new expression, and also include the column field that needs to be compared:

Comparison of value with Overall Average: IF ([Overall Column's Average Expression] < SUM([Column_Field]), 'LOW', 'HIGH')

Configure the Grid Widget

Drag and drop the grid widget onto your dashboard and configure it with the necessary fields, including the expression field created in Step 2. The widget will display “LOW” or “HIGH” based on the comparison result. You can customize these text indicators as per your requirements.

Apply Conditional Formatting

To visually distinguish the values, apply conditional formatting to the expression field. Use the formatting options to customize the color for each condition, such as red for “LOW” and green for “HIGH”.

Rendering the Widget

After applying the conditional formatting, the widget output will display the rows with the corresponding color indicators, making it easy to identify which values are above or below the average.

For a visual guide and additional conditions, please refer to the provided screenshots within the Bold BI dashboard.

Additional References

For more detailed instructions and other conditional formatting options, please refer to the following help documentation:

1. Documentation for Conditional Formatting: How to apply conditional formatting
2. KB Link: How to Utilize Conditional Formatting to Show Text instead of an Icon in Bold BI Grid Widget

By following these steps, you can effectively use conditional formatting in Bold BI to highlight data points in relation to a column’s average, enhancing the analytical capabilities of your dashboards.