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.

Table of Contents

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.

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.

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.

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.

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.

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.

Conclusion

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!

Brian