Power BI Aggregates - How to Stop Your Visuals From Silently Adding Up the Wrong Thing

Ask a room of Power BI users what "aggregation" means and most will shrug. Ask them why their sales total is showing an average instead of a sum, or why a column of ID numbers is being added together into a meaningless figure, and you'll get the same shrug. These are the same problem. Power BI aggregates fields automatically the moment you drop them into a visual, and it makes a decision about how to aggregate them whether you asked it to or not. When it guesses right, nobody notices. When it guesses wrong, you get a number that looks plausible and is completely incorrect.

For Australian businesses making real decisions off Power BI reports, this is one of those quiet risks that's worth understanding properly. A wrong aggregation doesn't throw an error. It doesn't turn a cell red. It just sits there being wrong, and if the figure is in the right ballpark, it can survive for months before someone notices the total doesn't reconcile against the finance system.

Microsoft's documentation on working with aggregates walks through the mechanics. What follows is the field-tested version, based on the aggregation mistakes we see over and over when we pick up a client's reports.

What aggregation actually is

Every time you put a numeric field into a visual, Power BI has to decide how to roll it up. A table with one row per sale, dropped into a bar chart grouped by month, can't show you every individual sale. It has to combine them into one number per month. That combining is aggregation, and the default for a numeric column is usually sum.

Sum is right most of the time for measures like revenue, quantity, or cost. Add up all the sales in March and you get March's total. Fine. The problem starts when the field isn't the kind of thing you should add up.

The classic example is anything that's a number but isn't a quantity. Ages. Temperatures. Percentages. Ratios. Store numbers. Postcodes. Power BI sees a column of numbers and cheerfully sums them, so your report shows a "total age" of 4,271 across your customer base, which is nonsense. Nobody wants the total of everyone's age. They want the average, or the count, or nothing at all. But the default reached for sum, and unless someone changed it, sum is what you get.

Changing how a field aggregates

The good news is that changing the aggregation is easy once you know it's there. In the visual, click the dropdown on the field in the values well and you can switch between sum, average, minimum, maximum, count, count (distinct), standard deviation, variance, and median. Pick the one that actually makes sense for the field and the visual updates.

So for a column of customer ages, you'd switch to average. For a list of order IDs where you want to know how many orders there were, you'd switch to count, or count distinct if there might be duplicates. For a "last reading" style field, maximum or minimum might be what you're after. The mechanics take two seconds. The hard part is remembering to look, because Power BI won't prompt you.

There's also aggregation for text and date fields, which people forget exists. Drop a text column into a visual and you can count it, or take the first or last value alphabetically. Dates can be aggregated to earliest or latest. This is genuinely useful for questions like "when was this customer's most recent order" without writing any DAX at all.

Where this quietly goes wrong

A few patterns come up again and again on client work.

Averaging an average. This is the one that burns the most people. Say you have a column of percentages, maybe a completion rate per store. You drop it into a card, set it to average, and get a number. It looks right. It's usually wrong. The average of a set of percentages is not the same as the overall percentage, because it doesn't weight by the underlying volume. A store that did 3 orders and a store that did 3,000 orders count equally in a plain average, which distorts the result badly. The fix is a proper DAX measure that divides total completions by total attempts. Aggregation in the visual can't do weighted maths, and pretending it can is a common source of subtly wrong dashboards.

Summing a semi-additive value. Stock on hand, account balances, headcount. These are numbers you can sum across categories but not across time. Total inventory across all warehouses today makes sense. Total inventory summed across every day this month does not, because you'd be counting the same stock over and over. The default sum will happily do the wrong thing across your date axis. This needs a measure that handles the time dimension deliberately, and it's exactly the kind of thing that gets missed until someone asks why the stock figure is ten times too high.

Implicit aggregation hiding a modelling gap. When you rely on the visual to aggregate a raw column, you're using what's called an implicit measure. It works, but it's invisible. Nobody can see the logic, nobody can reuse it, and if two reports aggregate the same column differently, you get two different answers to the same question with no obvious cause. Our strong preference, and this is a bit of a hill we'll die on, is to build explicit measures for anything that matters. Write the DAX, name it clearly, and use that everywhere. The visual then just displays a measure whose logic is defined once and consistent across every report.

What we recommend on client work

When we set up a Power BI model properly, we try to make the raw columns hard to misuse. For fields that should never be summed, like ages or IDs or postcodes, you can set a default summarisation of "Don't summarise" in the model itself, so that when someone drops the field into a visual it doesn't silently add up. You can also mark true measures explicitly and hide the raw numeric columns so users are guided toward the correct, pre-built aggregations rather than rolling their own.

This kind of guardrail is a small amount of work upfront and it prevents a whole category of wrong numbers downstream. It's part of what our Power BI consultants do when building a model that's meant to be used by people who aren't data specialists, which is most people. A good model makes the right thing easy and the wrong thing difficult. Leaving every field on its default aggregation does the opposite.

We also treat aggregation as part of report review. When we audit an existing set of reports, one of the checks is simply going through the visuals and confirming each field is aggregated the way it should be. It's tedious. It also routinely finds at least one figure that's been quietly wrong, and the client usually had no idea.

The honest take

Automatic aggregation is a genuinely good feature. It's a big part of why Power BI is usable by people who don't write code, and I wouldn't want it gone. Someone can drag a sales column onto a chart and get a sensible total without knowing what a measure is. That accessibility is the whole point.

But the flip side is that it makes wrong numbers easy to produce and hard to spot, because nothing warns you. The tool assumes sum is what you meant, and for the fields where that assumption breaks, the failure is silent. So the advice is boring but real: whenever you drop a numeric field into a visual, take one second to ask whether sum is actually what you want. Most of the time it is. The times it isn't are the times that matter.

If your reports have grown into something you're no longer sure you can trust, and you suspect there might be a few of these quietly wrong figures lurking, that's a very common situation and a fixable one. It's the kind of thing we handle as part of broader business intelligence and analytics work, often as a first step before any AI or automation gets layered on top. If you'd like a second opinion on the state of your reporting, reach out and we'll take a proper look.