How to create a dynamic category column in DAX: step by step

João Barros 14 de October de 2026 5 min read

This tutorial shows how to create a calculated column in DAX that classifies products or transactions into dynamic categories (for example: "Top", "Medium", "Low") based on business rules such as sales and margin. A category column helps segment analyses in Power BI and create more useful filters and visuals. We will explain why to choose a calculated column vs. a measure and give a practical example, with concrete values and attention to performance.

Prerequisites

Power BI Desktop or Tabular model with a fact table (for example, Sales) and a product table (Products).
Basic DAX knowledge: measures, calculated columns and functions like RELATED, CALCULATE, IF and VAR.
A sales column (Sales[Amount]) and, optionally, margin (Sales[Margin] or equivalent calculation).
A well-populated date table (Date) if you will use Time Intelligence; relationships between Sales[ProductID] and Products[ProductID] correctly defined.

Step 1: Define the categorization rule

Before writing DAX, decide the rules. Well-defined rules avoid ambiguous logic and enable testing. Practical example: categorize products by Total Sales in the last 12 months (L12M) and weighted average margin. Proposed criteria:

"Top": L12M Sales > €100,000 and average Margin >= 20%.
"Medium": L12M Sales between €20,000 and €100,000 (inclusive, average margin indifferent).
"Low": L12M Sales <= €20,000 or absence of sales.

These thresholds are examples; in a real context you should base them on percentiles (e.g., top 10% of products) or the company strategy. Deciding whether the categorization should be static (calculated column) or dynamic depending on the time filter (measure) is crucial — here we show the calculated column using aggregated measures to capture L12M.

Step 2: Create a measure for Total Sales (if not already present)

A measure is necessary for consistent calculation with context. Measures are efficient when used in visuals and support filters automatically.

Sales Total = SUM(Sales[Amount])

Step 3: Create a measure for Sales last 12 months

Using Time Intelligence allows comparing periods and avoids duplicating logic. Assuming you have a Date table with continuous columns, use DATESINPERIOD to calculate L12M. This ensures that if the user applies a date slicer, the measure responds correctly.

Sales L12M =
CALCULATE(
    [Sales Total],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

Step 4: Create a measure for average Margin per product (optional)

A sales-weighted average margin is more representative than a simple average. Use DIVIDE to avoid division by zero errors. If the company stores margin as a monetary value (MarginAmount), calculate the aggregated percentage like this:

Margin Avg =
DIVIDE(
    SUM(Sales[MarginAmount]),
    SUM(Sales[Amount]),
    0
)

The third argument of DIVIDE returns 0 when the denominator is zero. In real scenarios, we might want BLANK() to signal missing data.

Step 5: Create a calculated category column in the Products table

Now we create the calculated column in Products that evaluates each productID using the aggregated measures. Since measures depend on the row context, we use CALCULATE with FILTER to force the context by product. This pattern is common when you need to materialize the category by product.

Product Category =
VAR vSales =
    CALCULATE([Sales L12M], FILTER(ALL(Sales), Sales[ProductID] = Products[ProductID]))
VAR vMargin =
    CALCULATE([Margin Avg], FILTER(ALL(Sales), Sales[ProductID] = Products[ProductID]))
RETURN
IF(
    vSales > 100000 && vMargin >= 0.20, "Top",
    IF(vSales > 20000, "Médio", "Baixo")
)

Concrete example: if a product had €150,000 in sales in the last 12 months and a margin of 22%, it gets "Top". If it had €30,000 in sales with a 10% margin, it gets "Medium".

Step 6: Avoid common mistakes (division by zero, relationships, performance)

Points to watch:

Relationships: if Products and Sales are not related by ProductID, vSales will be BLANK(). Check directionality and cardinality.
Division by zero: use DIVIDE with a third argument (e.g.: 0 or BLANK()).
Performance: calculated columns in large models (for example, 100k+ products) can take time. Consider calculating offline or using an aggregated table. Measures are generally more efficient for dynamically filtered calculations.
Time context: a static calculated column is evaluated at creation time; if you want the category to change with the date slicer, use a measure or a calculated table with scheduled refreshes.

// Safe margin example
Margin Avg =
DIVIDE(
    SUM(Sales[MarginAmount]),
    SUM(Sales[Amount]),
    0
)

Validate the result

Place Products[Product Category] in a table visual along with Sales L12M and Margin Avg. Validate with samples: filter the previous year and confirm that a set of the 10 products with highest sales really appears as "Top". If you notice many "Low", review the thresholds or use percentiles (e.g., TOP 10% > median). If some rows are blank, check relationships and null values in Sales.

Conclusion

Creating a dynamic category column in DAX enables segmenting reports and facilitating analysis in Power BI. Use clear thresholds, test with representative datasets (e.g., 1,000 products, 100k transactions) and choose between calculated column and measure according to the need for temporal interactivity. Next steps: turn thresholds into "what-if" parameters to make them interactive, or generate an aggregated table if performance is critical.