Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create Buckets in Power BI Using DAX

Cody Schneider••7 min read

Grouping raw numbers into more digestible categories - like turning individual ages into age ranges - can instantly transform a confusing Power BI report into a clear and insightful one. This process, often called binning or bucketing, is essential for summarizing data and spotting trends. This article will walk you through exactly how to create these custom groups using DAX functions like IF and SWITCH.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Group Data into Buckets?

Working with raw, continuous data can be overwhelming. Imagine a bar chart showing sales by every individual customer age from 18 to 75. You'd have nearly 60 different bars, creating a visual that's difficult to interpret. By bucketing those ages into groups like "18-25", "26-35", "36-50", and "51+", you can simplify that chart down to four clear bars, making it easy to see which age demographics are your most valuable customers.

Bucketing helps you:

  • Simplify Visualizations: Reduce clutter on your charts and graphs, making them readable at a glance.
  • Improve Analysis: Grouping data enables you to compare performance across different segments, such as pricing tiers ("Budget," "Standard," "Premium") or lead scores ("Cold," "Warm," "Hot").
  • Uncover Trends: It becomes much easier to identify patterns. Are products in the "$50-$100" bucket selling better than those under $50? Bucketing will tell you that story clearly.

Understanding Where to Create Your Buckets: Calculated Columns

Before diving into the formulas, it's important to know where we'll be creating these buckets in Power BI. We'll be using Calculated Columns, not Measures.

Here's why: A calculated column adds a new column to your data table and calculates a value for each individual row. This is exactly what we need for bucketing - we want to evaluate each row's value (like a customer's age or a product's price) and assign it to a specific category (its "Age Group" or "Price Tier"). This new category then becomes a permanent attribute for that row, which you can use to slice and dice your data in visualizations.

Measures, on the other hand, perform calculations over many rows, like summing up total sales or counting customers. They are calculated on-the-fly based on the context of your visualization, making them unsuitable for assigning static, row-level categories.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Method 1: Using the IF Function for Simple Buckets

The IF function is perfect for simple, binary logic. If you just need to split your data into two categories, IF is the direct and easy way to do it.

Let's imagine you have a list of service tickets and you want to classify them as either "High Priority" if they've been open for more than 7 days, or "Normal Priority" otherwise.

Step-by-Step Guide:

  1. Navigate to the Data View in Power BI by clicking the table icon on the left-hand pane.
  2. Select the table containing the data you want to bucket (e.g., 'ServiceTickets').
  3. In the "Table tools" ribbon at the top, click New column. This will open the formula bar.
  4. Enter the following DAX formula and press Enter:
Priority = IF('ServiceTickets'[Days Open] > 7, "High Priority", "Normal Priority")

That's it! Power BI will now go through every row in your 'ServiceTickets' table. For each ticket, it checks if the value in the [Days Open] column is greater than 7. If it is, it assigns "High Priority" to the new 'Priority' column, otherwise, it assigns "Normal Priority".

Handling Multiple Buckets with Nested IFs

You can also use IF for more than two categories by "nesting" one IF function inside another. However, this method can get complicated and hard to read very quickly.

For example, let's group products into three price tiers: "Low" (under $20), "Medium" ($20 to $50), and "High" (over $50).

Price Tier (Nested IF) =
IF(
    'Products'[Price] < 20, "Low",
    IF(
        'Products'[Price] <= 50, "Medium", "High"
    )
)

This works, but as you can see, the formatting gets tricky. If you needed to add five or six buckets, the formula would become a tangled mess of parentheses. For any scenario with more than two or three categories, there's a much cleaner solution: the SWITCH function.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Method 2: Using the SWITCH Function for Cleaner, Multi-Category Buckets

The SWITCH function is the preferred method for creating multiple buckets in Power BI. It's more organized, easier to read, and simpler to modify than a long chain of nested IFs.

We'll use a powerful pattern: SWITCH(TRUE(), ...). This structure tells DAX to evaluate a series of logical conditions and return the value for the first one that evaluates to TRUE. This makes it perfect for creating sequential buckets.

Let's use our earlier example of creating customer age groups. We have a 'Customers' table with an 'Age' column, and we want to group them into the following buckets:

  • 18-25
  • 26-35
  • 36-50
  • 51-65
  • Over 65

Step-by-Step Guide:

  1. In the Data View, select your 'Customers' table.
  2. Click New column to open the formula bar.
  3. Enter this clean and readable DAX formula:
Age Group =
SWITCH(
    TRUE(),
    'Customers'[Age] <= 25, "18-25",
    'Customers'[Age] <= 35, "26-35",
    'Customers'[Age] <= 50, "36-50",
    'Customers'[Age] <= 65, "51-65",
    "Over 65"
)

Breaking Down the Formula

  • SWITCH(TRUE(),: sets up the function to look for the first true condition.
  • 'Customers'[Age] <= 25, "18-25",: If a customer's age is 25 or less, assign them to the "18-25" group and stop evaluating.
  • 'Customers'[Age] <= 35, "26-35",: If the first condition was false, DAX checks this one. If the age is 35 or less, they get put in the "26-35" group. This logic works because anyone 25 or under has already been filtered out by the first line.
  • 'Customers'[Age] <= 50, "36-50",: Similarly for the next range.
  • 'Customers'[Age] <= 65, "51-65",: And so on.
  • "Over 65": The default value if none of the previous conditions are met (meaning age is over 65).

The result is a new 'Age Group' column that you can drag into any visualization to analyze your data by these defined segments.

Practical Tips for Creating Your Buckets

Now that you know how to build the formulas, here are a few tips to make your buckets more effective.

Get to Know Your Data's Range

Before writing any DAX, get a feel for your data. What are the minimum and maximum values you need to bucket? In Power BI Desktop's Data View, you can click the header of a numerical column to see basic descriptive statistics, including min and max. This helps ensure your buckets are relevant and don't miss any values.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Correct the Sort Order

By default, Power BI sorts text-based buckets alphabetically. So, if your price buckets are "Low", "Medium", and "High", Power BI will display them as "High", "Low", and "Medium." To fix this, you can create a second, numerical sorting column.

  1. Create a helper column named 'Tier Sort':
Tier Sort Order =
SWITCH(
    TRUE(),
    'Products'[Price Tier] = "Low", 1,
    'Products'[Price Tier] = "Medium", 2,
    'Products'[Price Tier] = "High", 3,
    4
)
  1. Select the 'Price Tier' column in the Data View.
  2. Go to the "Column tools" ribbon and click Sort by column.
  3. Choose 'Tier Sort Order' from the dropdown. Now your visuals will show "Low", "Medium", "High" in the correct order.

Plan for NULLs and Outliers

What happens if some rows have blank values for the column you're bucketing? The default value in your SWITCH statement can act as a catch-all. You might want to label this something like "Unknown" or "Not Specified" so you can easily identify and filter them.

Final Thoughts

Creating buckets in Power BI with DAX is a fundamental skill that elevates your reports from simple data dumps to powerful analytical tools. By using a calculated column with the simple IF function or the much more versatile SWITCH function, you can transform continuous numerical data into meaningful categories that tell a clearer story and make your visualizations easy to understand.

While mastering DAX is a valuable skill, sometimes you just need to get answers from your analytics without writing formulas or setting up dashboards. When you need insights in seconds, we built Graphed. You can connect your marketing and sales tools and simply ask questions in plain English like, "group last month's Shopify revenue into $50 price buckets," and it instantly generates the chart for you. It's a great way to handle the manual report building and get back to making data-driven decisions.

Related Articles