Matrix Visual In Power BI: Controling Totals & Subtotals

Today, I’ll show how you can control totals and subtotals in your matrix visual in Power BI. You can watch the full video of this tutorial at the bottom of this blog.

This is a problem posted by one of our members at the Enterprise DNA forum. The user wanted the columns to look like the excel pivot table tabular format with the grand total at the bottom, but not the subtotal. I’m going to share a couple of ways how you can solve this. One is through a matrix visual and the other is through DAX.

Creating A Matrix Visual In Power BI

What I did was I started off with the Enterprise DNA Practice Dataset External Tool, which is a tool that we developed for exactly this purpose to create practice datasets. It creates a three-year data set with a full data model up to today’s date. It’s a basic star schema data model, but it can be used to develop a lot of good testing models.

I just put together a matrix visual, which is very similar to what the member wanted in the forum question.

matrix visual in power bi

I’ve used a very simple Total Sales measure to SUM of Line Sales within the Sales table. I’ve kept that to two products, three different channels, and then set that to four quarters within the year 2020.

matrix visual in power bi

As you can see, I made four different copies of this table. Then, using the technique that I’m going to show you, you can keep the row totals and eliminate everything else, keep the column totals and eliminate everything else, keep the subtotals and vary that by individual subtotal, or take out all the subtotals and all the grand totals and just be left with the base rows. So, you’ve got full flexibility and control here, and I’ll show you how this is done.

Now, if we go to the main table, click on it, and then we go to the format, there’s an option that is per row level. Turn that on. There’s also one per column level and we make sure that’s turned on as well. We can also turn on Product Name, which turns on the grand totals at the bottom. The Channel Name turns off the subtotals, and the Quarter and Year turn off the row totals.

matrix visual in power bi

You just turn those on and toggle the options. That’s the really simple and easy way to do it. The more difficult way is through DAX.

DAX Solution To Control Totals & Subtotals In A Matrix Visual In Power

You may be wondering, if we’ve got this really simple way to do this, why should we even bother with a more difficult way? It all boils down to the issue that comes up a lot in terms of incorrect totals.

And so, in this case, I’ve stripped down the example from the previous page to just quarter one and quarter two, but otherwise, it’s the same matrix visual.

matrix visual in power bi

We’ve got the simple Total Sales measure and that works fine, the totals are correct. But then, if we use a more complex sales measure here, Total Sales SWITCH, this is dependent on the value of our sales channel.

I just made up some numbers to make a point here that if we have a more complex Total Sales calculation, the numbers are incorrect (below table on the right side). If we look closely, the prior numbers, which are correct, are the same, and yet it can’t be true. When we look at export, for example, it’s 30% higher than in the previous case and yet the totals here are showing up the same. That just can’t be correct.

So, how do you correct that?

To do this within DAX, you can use a whole series of functions, which I’ve laid out here. We have HASONEFILTER, HASONEVALUE, ISFILTERED, ISCROSSFILTERED, and ISINSCOPE. These are all used to determine among other things, whether you’re in a data row a subtotal row, or a total row.

As you can see, they all look the same with the exception of HASONEVALUE, where it’s showing the product one subtotal. That’s just because of an artifact in the data that I’ve developed for this one, where product one only has a distributor channel. It doesn’t have an export or wholesale channel in the Sales table.

They’re not showing the grand total and the subtotals, but if we impose a slicer on this, for example, let’s pick Distributor and Wholesale, suddenly these tables change a lot.

matrix visual in power bi

And now, there’s a big difference between these tables. HASONEFILTER here doesn’t show the product one subtotal while HASONEVALUE does. ISFILTERED now shows the grand total, the row totals, and the subtotals as does ISCROSSFILTERED. ISINSCOPE, on the other hand, provides a result that’s very similar to HASONEFILTER. You can see that the imposition of that additional slicer changes the nature of what you get here.

There’s not a hard and fast answer to what you should use to fix a given total. It’s going to depend on your data model. It’s going to depend on your DAX. But the important thing here is to see that the different functions produce different results in terms of blanking out the totals versus the subtotals, and the grand total.

***** Related Links *****
Using Advanced Logic In Power BI To Correct Your Totals
How To Create Unique Subtotals In Power BI Tables
How To Fix Matrix Totals In Power BI


The specifics of that are beyond the scope of this tutorial. Check out other tutorials on the links below for more related content. Just note that there are also oftentimes performance distinctions that you’ll find between these different functions and that will also speak to which one you choose in your ultimate measure.

I’ve shown you two techniques that you can implement to control totals and subtotals in your matrix visual in Power BI. Hopefully that gives you some good, useful tools for your toolbox.

All the best!


Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More