Skip to main content
Version: 9.2

Dynamic Bucketing Using Formulas

Dynamic bucketing lets you define bucket thresholds with values that change across rows, enabling categories to adapt to each record’s data instead of relying on fixed cutoffs.

Overview​

Traditional buckets use static thresholds, which is practical when everyone shares the same breakpoints. However, they fail when thresholds vary by product, region, or other attributes. Dynamic bucketing references other columns for the cutoff values.

Benefits​

Dynamic buckets make bucket thresholds part of the data itself, which changes how you categorize data. For inventory management, it can produce more accurate alerts and fewer false positives when deciding what to reorder. Each product can have its own reorder point and safety stock. By building buckets with formulas, you can generate meaningful categories like Good, Sufficient, and Low Stock levels that update automatically as thresholds change.

Dynamic Bucket Tutorial​

The following tutorial video shows how to create dynamic buckets with changing values using formulas.

Tags: Charts, Buckets, Formulas

Example​

The following example categorizes inventory using safety stock and reorder point. The dataset includes a Quantity column and two threshold columns: SafetyStockLevel and ReorderPoint. Each product row uses its own values for these thresholds.

In this case, create a new column, Quantity Stock Level, that categorizes inventory into one of the following labels:

  • Quantity — Quantity at or above safety stock.
  • SafetyStockLevel — Quantity below safety stock but above reorder point.
  • ReorderPoint - Quantity at or below reorder point.

These categorized labels are your dynamic buckets.

Build Buckets Using a Formula​

Use standardized formula syntax (similar to Google Sheets or Excel). The approach uses the IFS function to evaluate multiple conditions in order, and the AND function to help with conditions.

For example:

  • If Quantity >= SafetyStockLevel, label as Good stock level.
  • Else if quantity < safety stock and quantity > reorder point, label as Sufficient stock level.
  • Else if quantity <= reorder point, label as Low stock level.

Give the new column a descriptive name like Quantity Stock Level. The formula produces a column that can be used like any other for slicing and aggregating.

Sample Standardized-Syntax Formula​

> IFS( Quantity >= SafetyStockLevel, "Good Stock Level", AND(Quantity < SafetyStockLevel, Quantity > ReorderPoint), "Sufficient Stock Level", Quantity <= ReorderPoint, "Low Stock Level" )

Syntax notes:

  • IFS evaluates conditions in order and returns the matching result for the first true condition.
  • AND takes multiple conditions and returns true only when all are true. In standardized syntax, logical operators like the ampersand (&) are not used; use the AND function instead.
  • Use >= and <= for inclusive thresholds where appropriate.

Use the New Buckets in Visualizations​

After creating the new formula column, it can be used as a series or category in charts. For example, a multi-series bar chart by product line with the series set to Quantity Stock Level shows how inventory is distributed across the Good, Sufficient, and Low categories per product line.

Because the thresholds are row-specific, the chart reflects the real status of each product without manual threshold adjustments. This is particularly helpful for identifying which product lines need attention or restocking.

Checklist​

  • Confirm that the dataset contains the threshold columns (for example, SafetyStockLevel and ReorderPoint).
  • Decide the bucket labels and their logical order (highest-priority condition first).
  • Implement the formula using IFS to express ranges.