This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

Stop automatic grouping in Power BI Desktop
Read this blog to understand the risk of Power BI's automatic grouping of data, and to learn how to avoid getting caught out!

Posted by Sam Lowrie on 18 July 2018

Preventing automatic grouping in Power BI Desktop

For those new to aggregation in programmes other than Excel, it's worth noting what happens when using aggregates in visuals in Power BI. To demonstrate this, consider this table:

A simple table listing the number of products sold in each store.

The Problem

Everything looks fine at first glance, although Arndale Centre looks a bit of an outlier. Maybe it is in London? Let's check the towns these centres belong to:

Uh oh! Turns out that there are several centres called Arndale Centre - Power BI has grouped the sales to centres of the same name.

You can see what's going on more clearly if you count how many times each Centre appears:

Each centre has its own row in the table, so we can check if the same name appears multiple times.

Creating or using a unique centre identifer

The easiest way to fix this problem is to use a unique identifier for each centre within the table visual to split up centres with the same name. We already have a unique column called CentreID:

Make sure to un-aggregate the column by choosing Don't Summarize, otherwise Power BI will try to SUM or COUNT it.

If you don't have a nice ready-made column to hand, not to fear. We can add one pretty easily in Query Editor. First click this button:

The Edit Queries button on the Home tab.

We could do a similar thing by using the RAND or RANDBETWEEN functions in a calculated column.

Select the table you want to add the new unique column to (for us it is the Centre table). Then choose Index Column from the Add Column tab:

Index column aggregating Power Bi Desktop

It is never recommended to use From 0 unless you have a good reason to do so!

Using the unique identifier to suppress grouping

You can now use your new column to stop the aggregation of centres with the same name:

Remember to change the aggregation to Don't Summarize, or all your hard work will be for naught!

If you don't want to see the column simply drag the column width so small you can't see it, just like you would in Excel:

You will need to turn Word wrap off for both the Column headers and Values, or risk getting really long column headers!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Preventing automatic grouping in Power BI Desktop

The Problem

Creating or using a unique centre identifer

Using the unique identifier to suppress grouping

Head office

London

Manchester