Grouping and Binning in Power BI

1. Grouping & Binning In Power BI https://www.bensound.com/ (energy) https://www.bensound.com/

2. Making Raw Data Easier to Understand Real‑world data is messy, and totals rarely tell the full storyTo understand what’s really happening, you need to see how values spread across ranges — not just the final sums. That’s where grouping and binning help They organise granular data into clear patterns, reveal trends, and make your visuals far easier to interpret.

3. What Are Grouping and Binning? • Grouping • Grouping lets you combine multiple categories into logical clusters, so your data is easier to analyse and present. • Examples • Merging several product types from “Other Components” into a clearer subcategory • Grouping individual State/Province entries into broader Country‑level categories • It’s ideal when you want to simplify long lists or tidy up inconsistent categories.

4. Example - Grouping

5. How the Group Looks When Used in a Visual Breaking these out from ‘Other Components’ allows us to surface meaningful differences between product types and gives a more accurate picture of category performance across regions.

6. How the Group Looks When Used in a Visual This chart shows how the Country group can be used to filter the report, with the grouped field limiting the view to sales for the United Kingdom only (or other countries).

7. How to Create a Group in Power BI Step 1 — Choose the field you want to tidySelect a categorical column such as product type, region, or state‑province. Step 2 — Create the groupRight‑click the field → New Group

8. How to Create a Group in Power BI Step 3 — Select items to groupIn the Ungrouped values list, highlight the items you want to combine → click Group Step 4 — Name your group Give the new group a clear name such as Other Components (Groups) or State‑Province (Country Groups). • Examples: • Combine several small product types into Other Components • Group individual state‑province entries into Country buckets

9. How to Create a Group in Power BI Power BI creates a new field you can use in slicers, visuals, and hierarchies.

10. What is Binning? • Binning creates numeric ranges • for example: Bin Size 250 • This gives you ranges like: • £0 – £250 • £250 – £500 • £500 – £750 • £750 – £1,000 • …and so, on up to £3,500+

11. How Binning Looks When Used in a Visual This visual shows how much sales value comes from products whose list price falls into each bin. Power BI automatically assigns each record to the correct range, giving you a structured view of how your values are distributed.

12. Why Binning Matters • Raw numbers can be overwhelming… • Binning helps you answer questions like: • Where do most of my values sit? • Are there many small items or a few large ones? • Is my data tightly grouped or spread out? • It’s a simple way to reveal the shape of your data — something totals alone can’t show.

13. Binning Example For example: “By binning our list prices, we can quickly see that most of our sales come from products priced around £2,500, with much smaller totals in the lower price brackets

14. How to Create Bins in Power BI • Step 1 — Choose your numeric field • Select the column you want to analyse (e.g., sales amount, list price) • Step 2 — Create a bin • Right‑click the field → New Group → choose Bin. • You can define: • Bin size (e.g., £100 increments) • Number of bins Power BI creates a new field such as List Price (bins).

15. How to Create Bins in Power BI • Step 3 — Add the bin to a visual • A column chart or bar chart works perfectly for a frequency distribution. • Axis → your bin field (List Price – Bins) • Values → Sum or count of your original field (Sum of Sales) You now have a clear view of how your values fall across ranges.

16. Add a Cumulative Frequency Distribution (Optional) • Why use a cumulative distribution? • Once your bins are in place, a cumulative view shows how values build up across the ranges. • It highlights thresholds, concentration, and how quickly totals rise. • Cumulative frequency distribution answers questions like: • • What percentage of expenses fall under £200 • • Where 80% of invoices accumulate • • How quickly values rise across the bins • It’s a simple but powerful way to reveal thresholds and concentration.

17. Cumulative Frequency Measures A basic cumulative measure: Cumulative Frequency = CALCULATE( COUNTROWS('Table'), FILTER( ALLSELECTED('Table'[Amount (bins)]), 'Table'[Amount (bins)] <= MAX('Table'[Amount (bins)]) ) ) Cumulative percentage: Cumulative % = DIVIDE( [Cumulative Frequency], CALCULATE(COUNTROWS('Table'), ALLSELECTED('Table')) ) You can either add a cumulative line to your existing column chart or create a separate cumulative chart using the same measures to compare both views.

18. Comparing the Cumulative Distribution Across Price Bins The dotted line shows that half of the sales are less than £2,000 list price

19. Tips for Cleaner, More Insightful Bins • Keep bin sizes consistent • Avoid too many bins (8–15 is usually ideal) • Rename bins for clarity (e.g., “£0 – £100”) • Sort bins numerically, not alphabetically • Add tooltips to show exact cumulative values • These small touches make your visuals feel polished and professional

20. Final Thoughts Grouping and binning quickly turn raw numbers into clear, meaningful patterns. Add a cumulative frequency view, and you can see how values build across ranges for deeper insight. Whether you’re analysing sales, expenses, operations, or customer behaviour, these tools help you move beyond totals and into real understanding.

21. Credit: elle.harrison@selectdistinct.co.uk  Want to unlock more resources? Visit our Power BI Glossary or Business Analytics Blog