How to Create Level of Detail (LOD) Calculations in Bold BI

Introduction

Level of Detail (LOD) calculations allow you to perform aggregations at varying levels of data granularity — for example, calculating customer-level totals within a region or overall averages within a category.

While Bold BI does not have built-in LOD syntax, you can achieve the same analytical depth using SQL window functions in Code View mode within the Data Source Designer.

This article explains how to create LOD-style calculations in Bold BI using a sample sales dataset, generate the computed columns, and visualize the results in a dashboard.

LOD Calculations Overview with Sample Sales Data

For this demonstration, the sample dataset contains the following columns:

This data helps analyze customer, regional, and product-level performance.

Using SQL-style window functions, calculated columns can be created within the code view option of the Bold BI data source to provide deeper insights into the data at multiple levels.

Creating LOD Calculations in Code View Mode

Follow these steps to generate LOD-based columns directly within your data source:

1. Go to Data Source Designer in Bold BI.
2. Switch to Code View Mode.
3. Use the following SQL query to define your LOD calculations.

SELECT 
  Region,
  CustomerName,
  SUM(Sales) AS CustomerSales,
  SUM(SUM(Sales)) OVER (PARTITION BY Region) AS RegionSales,
  SUM(Sales) * 1.0 / SUM(SUM(Sales)) OVER (PARTITION BY Region) AS PercentOfRegion,
  SUM(Sales) OVER (PARTITION BY CustomerID) AS CustomerLifetimeSales,
  MIN(OrderDate) OVER (PARTITION BY CustomerID) AS FirstPurchaseDate,
  RANK() OVER (PARTITION BY Region ORDER BY SUM(Sales) DESC) AS ProductRank,
  AVG(Sales) OVER (PARTITION BY Region) AS RegionAvgSales
FROM SalesData
GROUP BY Region, CustomerName, CustomerID;

This query computes several LOD-style metrics such as:

• Regional contribution
• Lifetime sales per customer
• First purchase date
• Top product rank
• Regional average sales

Executed Query Result

Once executed in Code View Mode, the query generates a dataset with all LOD calculation columns.

Dashboard Created from LOD Data

Using the dataset with generated LOD columns, a sample dashboard was built to visualize:

• Customer contribution by region
• Lifetime sales trend
• First purchase timeline
• Top products by region
• Regional average benchmarks

The dashboard showcases multiple widgets combining customer, regional, and product-level analysis, each powered by LOD-based computed fields.

Summary

By leveraging SQL window functions in Code View Mode, users can create Level of Detail (LOD) calculations to:

• Analyze data across multiple levels of granularity
• Derive deeper insights from existing datasets
• Build flexible dashboards with advanced calculated measures

This approach ensures a scalable and customizable way to perform detailed data analysis directly within Bold BI.

Additional References

• Bold BI Documentation
• Code View Mode