The problem: a Total row that adds up numbers it shouldn't
If you've built a matrix or table visual in Power BI with more than one type of number in it, you've probably run into this: you have columns like Sales and Units, where a Total row summing everything up makes perfect sense - and then you have a column like Discount % or Average Order Value, where summing doesn't make sense at all. Add up ten rows of "15%, 20%, 12%, 18%..." and Power BI happily shows you a Total of "142%" - a number that means nothing to anyone looking at the report.
This is one of those problems every report builder eventually hits, usually right after a client or manager asks, "why does the total say 142%?" It's not a bug - it's just that Power BI's default behavior is to sum every numeric column in a Total row, whether that math makes sense for that particular column or not.
The old workaround
Until recently, fixing this meant writing a separate measure just for the total row's sake - something using a pattern like ISINSCOPE() or HASONEVALUE() to detect "am I looking at the total row right now?" and, if so, calculate a different number (like a proper weighted average) instead of a plain sum. It worked, but it meant every percentage or ratio column in your model potentially needed its own custom "total-aware" measure, adding complexity that had nothing to do with the actual business logic - it existed purely to patch a display quirk.
What Power BI's June 2026 update actually changed
Power BI now gives you direct control over how each column's total is calculated, right from the matrix or table visual itself — no extra measure required. Instead of every column defaulting to "sum everything," you can set an exception per column and choose a different way to calculate that column's total, such as:
- Average - a more sensible choice for percentages, rates, or ratios
- Minimum or Maximum - useful for columns like "lowest price" or "peak inventory"
- A custom rule for cases where even Average isn't quite right
The key idea is that this is a formatting-level setting on the visual, not something that requires touching your semantic model or writing new DAX. You're telling the Total row, "for this specific column, calculate it differently" - and the rest of the matrix behaves exactly as before.
A real example
Say you've built a matrix showing Region down the rows, with three columns: Sales, Units Sold, and Discount %. The Total row correctly sums Sales and Units Sold - that's expected and useful. But the Discount % column's total currently shows something like "184%," because Power BI is summing five regions' discount percentages instead of averaging them.
Here's roughly how you fix it:
1. Select your matrix visual, and click on the Discount % column (or its field in the Values well).
2. In the Format pane, look for the Column totals section (this may also be reachable by right-clicking the column header, depending on your Power BI Desktop version).
3. Instead of leaving it on the default Sum behavior, choose Average as the exception for this column only.
4. Leave Sales and Units Sold untouched - they keep summing normally.
Now your Total row shows a genuinely meaningful average discount percentage, while the other columns still total correctly, and you didn't have to write a single line of DAX to get there.
Where this saves the most time
- Percentage and rate columns - discount %, margin %, conversion rate - anywhere a sum has never made sense but was the only default option
- Price or cost columns where you actually want the total row to show a maximum or minimum, not a sum
- Reports handed off to less technical teammates, since the fix now lives in the visual's formatting instead of a DAX measure someone else has to understand and maintain later
What to keep in mind
This is a display-level fix - it changes how the Total row is calculated for that visual, not the underlying data or measure itself. If the same measure appears in a different visual without this setting applied, it'll go back to summing by default there. So it's worth applying this exception on every visual where that column shows up, not just the first one you notice it in.
If you've been quietly living with a wrong-looking total row, or maintaining a pile of "total-aware" measures just to work around this, this is worth checking the next time you're in a matrix's formatting options.
Have you run into a percentage column total giving you a strange number before? I'd like to hear how you worked around it - drop a comment
Super User