Power BI Matrix | How To Fix Matrix Totals In Power BI

For this tutorial, I’m going to show you how to fix Power BI matrix totals, particularly fixing totals and subtotals if the measures you create don’t produce the right results originally. You may watch the full video of this tutorial at the bottom of this blog.

There are two reasons why I wanted to address this problem. One is that it occurs fairly often. It’s something that comes up a lot in the Enterprise DNA forum. We see this question repeated over and over in different forms. The second reason is that when you address it in the context of a matrix, it’s probably the most difficult of the broken totals problems.

If you learn how to address it in the matrix, you’ll know what you need to do to fix your totals in cards, in tables, and in other visuals where totals may break. I’m going to go through a systematic process for fixing Power BI matrix totals that are broken and really break that down by components.

In this example, the measure was complex enough that it broke the totals in a fairly spectacular way. I wanted to start off with really broken totals to show you how to fix even the worst of the broken totals problems.

Table of Contents

Incorrect Totals In Power BI Matrix

Let’s look at the context of the measures in this example. First, we have a measure called Spread Revenue, which is the branch of Total Opportunity Revenue and Lookup Scaling Factor.

The Total Opportunity Revenue is just a simple SUM measure, while the Lookup Scaling Factor is a more complicated, kind of a multi-criteria Lookup that we do base on a series of filter conditions.

This is just an example of a way that totals can break. Now if we take that Spread Revenue and drop it into the field for the matrix, this is what happens (see below).

As you can see, the base rows calculate properly, but the total rows don’t calculate it. In many cases, when you have totals, subtotals, and grand totals, Power BI will at least take a guess. And oftentimes, that guess is wrong, and you’ve got to fix the totals.

But in this case, Power BI just gives the equivalent of the shrug. It doesn’t have any idea what those totals should be. It doesn’t even have a guess. It just labels those as blank. To solve this, we’ve got to build some additional logic here that tells Power BI what to do in the row totals, the column totals, and the grand totals.

In many cases, where you’re looking at multiple conditions, you could do this as a nested I F statement. But we’ve got a better construct in Power BI that makes it a lot easier to see the logic, which is the SWITCH TRUE statement.

Fixing Power BI Matrix Totals With SWITCH TRUE Logic

We’ve got a SWITCH TRUEstatement that is built from the general to the specific, and I want to show you why this doesn’t work. What SWITCH TRUE statement does is that it goes through each of the conditions. It exits out of the first one that’s true.

And so, in this case, we go from the general. It says, “if animal’s class is in monotremes, marsupials, and placental, then it gets the label of mammal”. In this example, these are the only three classes of mammals. If it’s a mammal, it’s going to be one of these three types.

If we look at what happens, the data comes in and we go to SWITCH TRUE. And if it’s one of these classes, it gets “mammal”. This means that it’s never going to get down to this criterion here of marsupial or the one that we want, which is kangaroo because these are all mammals. And so, if this statement is true, it’s going to exit out.

If the statement is not true, then the two statements are not going to be true. It’s going to go to the default, which is not a mammal. So instead of building out from the general to the specific, you have to build your SWITCH TRUE statement from the specific to the general, and then the catch-all at the end.

If we take this back to the example of fixing matrix totals, it’s really the same thing. The A here is the specifics, which is going to be the first statement in our SWITCH TRUE. And then, we’ve got D as the catch-all. In the middle, we’ve got these two B and C conditions. B has context for the column, but not the row, while C has context for the row, but not the column.

Now let’s jump into the Tabular Editor 3 (TE3), which is really my favorite way now of debugging and understanding measures.

Debugging Using Tabular Editor 3

I call this measure (below) Spread Revenue with Fixed Totals. It’s quite long and looks like complicated DAX, but I’m going to break this down by component.

We’ve got here just two variables that basically pick up the SELECTEDVALUE of our Short Month and our Period, and that’s going to determine context. Then, we’ve got a virtual table, which is basically a virtual version of the matrix through this ADDCOLUMNS and CROSSJOIN construct.

If we look at this inside a DAX query, you can see that it basically creates every possible combination of Period and Short Month, which is what the matrix does.

Within the Tabular Editor 3, there’s another way of visualizing this called the Pivot Grid. It produces a visual that is equal to that matrix visual in Power BI.

Going back to our measure in the Expression Editor, we need to build the logic that creates the totals fields. And so, we’ve got this SWITCH TRUE statement with HASONEVALUE, wherein we’re testing to see whether or not there’s a context in each of the two fields that we need context in.

The first condition, which is from the most specific to the least specific is that condition A, the base rows. So, if we have context for Period and we have context for Short month, then we just use the Spread Revenue measure because that was producing the correct results in those data rows.

Now we need to build the logic for B, which is where we’ve got context in the column. So, we have HASONEVALUE for Short month, but not for Period. Now we want to force the logic, take the virtual matrix, and take our Spread Revenue measure, and then sum it across all the values of Period.

The next thing we’ve got is the flip side, where we’ve got HASONEVALUE in Period, but not in Short Month. We’re now taking the SUMX of the virtual table (Spread Revenue measure), and we’re summing it over all of the VALUES of Short Month.

And then finally, we’ve got the catch-all measure, which is if we don’t have context in either of the two, then we just take the total sum of the virtual table.

Now if we take our Spread Revenue with Fixed Totals measure to the fields and drop it into the values, we get the proper totals for the row totals, the column totals, and the grand totals. The SWITCH TRUE logic is going through and imposing all the logic we need in order to get the correct totals.

Conclusion

In this blog, I’ve shown you how to fix incorrect totals in the Power BI matrix. This is a construct that you can impose on a table or on a card, where you build the virtual logic for the base rows, which typically will be working if you’ve got a correct measure. And then, just impose the logic that you need in order to force the proper total.

It looks complex but definitely helpful. I hope this is all clear. You can watch the full video tutorial below and check out the links for more related content.

All the best!

Brian