How to configure Master-Listener Widgets to achieve 100% Total Display in the Listener Widget?

Published:

In dashboard design, especially when dealing with hierarchical data, it’s common to have a master grid widget displaying a summary and a listener grid widget providing detailed percentages that should add up to 100%. This article outlines how to set up a master-listener widget relationship in Bold BI where the listener widget’s percentages are based on the filtered values from the master widget.

Understanding the Grid Widgets Relationship

In Bold BI, a master grid widget can be used to filter data in other connected widgets, often referred to as listener widgets. To have the listener widget display percentages that sum up to 100%, we must use specific expressions in the listener widget’s data configuration.

Configuring the Listener Grid Widget

In this example, the master widget is configured to display the total count of records grouped by a specific category. This widget acts as the primary filter provider. The listener widget is designed to show the count of records by subcategory (module). To ensure that the percentages add up to 100%, specific expressions are used. Please refer to the following screenshot to see the database table for reference.

To ensure that the listener grid widget displays the correct percentages, follow these steps:

Expression for Counting Articles: First, create an expression to count the number of articles by subcategory.
```
Expression1: COUNT([ArticleNumber])
```

Expression for Total Count: Next, calculate the total count of articles across all subcategories. The OVER () clause is used to perform a window calculation over the entire set of data.
```
Expression 2: SUM(COUNT([ArticleNumber])) OVER ()
```

Expression for Percentage Calculation: Finally, divide the count of articles in each subcategory by the total count to get the percentage.
```
Expression 3: [Expression1]/[Expression2]
```

This final expression should be integrated into the Listener Grid widget to show the percentage of articles by subcategory.

How the OVER () Clause Works

The OVER () clause is a powerful feature in SQL-like query languages that allows you to perform window functions. It defines a window or set of rows within a query result set. A window function then computes a value for each row in the window.

In the context of the listener grid widget, OVER () is used without any partitioning, which means it considers all the rows as a single group. The SUM(COUNT([ArticleNumber])) OVER () expression calculates the total count of articles across all subcategories. This total is used as the denominator in the percentage calculation, ensuring that the percentages in the listener widget always sum up to 100% regardless of the filter applied in the master widget.

Conclusion

By using the expressions outlined above, you can effectively link a master widget to a listener widget in Bold BI, ensuring that the detailed percentages always sum up to 100%. This setup is vital for providing accurate and meaningful insights in your dashboards.

Please refer to the following screenshots:

Initial view: