Creating a Concentration Matrix Chart in Bold BI

Creating a concentration matrix chart, such as one that displays the sum of revenue for the top N clients, can be a valuable tool for visualizing client data in a meaningful way. In Bold BI, this can be achieved by utilizing custom SQL queries within the code view mode. Below is a step-by-step guide on how to create such a chart using a Microsoft SQL Server (MSSQL) database.

Prerequisites

• A table in the MSSQL database named “Clients” with columns “ClientName” and “Revenue”.
• Access to Bold BI with permissions to create and edit data sources and dashboards.

Steps to Create the Concentration Matrix Chart

Setting Up the Data Source

1. Create a new data source using the “Clients” table from your MSSQL database.
   For Example: Assume we have a table in the MSSQL database like the following data. Sample Data

Writing the Custom SQL Query

2. Switch to code view mode on the data source designer page.

3. Enter the following custom SQL query:

WITH RankedClients AS (
    SELECT
        ClientName,
        SUM(Revenue) AS TotalRevenue,
        ROW_NUMBER() OVER (ORDER BY SUM(Revenue) DESC) AS Rank
    FROM
        Clients
    GROUP BY
        ClientName
)
SELECT
    'Top3' AS Category,
    SUM(CASE WHEN Rank <= 3 THEN TotalRevenue ELSE 0 END) AS TotalRevenue
FROM
    RankedClients
 
UNION ALL
 
SELECT
    'Top5' AS Category,
    SUM(CASE WHEN Rank <= 5 THEN TotalRevenue ELSE 0 END) AS TotalRevenue
FROM
    RankedClients
 
UNION ALL
 
SELECT
    'Rest' AS Category,
    SUM(CASE WHEN Rank > 5 THEN TotalRevenue ELSE 0 END) AS TotalRevenue
FROM
    RankedClients;

Utilizing a Summary Table or View

Alternatively, if you have a summary table or view that calculates total revenue for clients in a ranked manner:

1. Connect to the data source and add the view or summary table.

2. Create expressions using the following expression texts:

Configuring the Chart Widget

3. Configure these expressions in the chart widget on your Bold BI dashboard.

4. To calculate the percentage, use the following expressions:

5. Set “percentage” as the measure formatting type for the percentage expression in the widget.

Conclusion

By following these steps, you can create a concentration matrix chart in Bold BI that highlights the sum of revenue for the top N clients. This visualization can help in making informed business decisions based on client revenue data.

Additional References

• Bold BI Documentation