How to Manipulate the Total in Power BI

Author:Murphy | View: 24000 | Time: 2025-03-22 20:47:18

In most cases, the total aggregates the detail rows in a visual, such as a table. But what if the total should show something different? I will discuss this challenge in detail here.

Introduction

To start, let's look at this report page:

Figure 1 – Base Report with Sum and Average Measures (Figure by the Author)

There is nothing special in this Matrix visualization.

Now, let's assume that I want to calculate a different Average for Sales in the Total columns and line. For example, a weighted average.

I will not dig deeper into how to calculate a weighted average, as I already wrote a piece about this. I add a link to it in the References Section below.

To do this, I must first understand how the Total is calculated. Then, I can start manipulating it.

So, let's start.

The Filter context

Even if you are already familiar with the Filter Context in Dax, I encourage you to read this section anyway, as I will define some terms used later.

OK, let's return to the Matrix visualization shown above.

For example, how are the Measures for the "Contoso Paris Store" and the "Economy" Product class calculated?

I select arbitrary cells to explain what happens in more detail for them:

Figure 2 – Base report with highlighted cells (Figure by the Author)

The results for the marked cells are calculated after applying the following filters defining the filter context.

State: Seine (Paris)
City: Paris
Store: Contoso Paris Store
Product Class: Economy
Year: 2024
Month: April
Continent: Europe
Country: France
Manufacturer: Proseware Inc.

All these filters influence the result for these specific cells.

When we separate the filters from "inside" the Matrix and from the Slicers around, we can define "internal" and "external" filters:

Figure 3 – Base report with internal and external filters (Figure by the Author)

You can see the internal Filter marked with red arrows and the external filters marked with blue arrows.

It doesn't matter where the filters are coming from for the measure. However, we need to understand the difference between these two filter sources, as in most cases, we can only react to changes from the "internal" filter when calculating the total.

So, what if the Filter Context for the Totals?

Think about it for a minute before continuing.

The Row- and Column-Totals are the calculations of the Measure in the absence of the Row- and Column Filters.

The Grand Total results from the absence of all "internal" filters.

All external filters are still active.

Consequently, we can check if filters are applied to manipulate the total.

Proof of Concept

To prove this statement, I change the Measure to have this code:

Avg Retail Sales = 1

This Measure's result will be 1, regardless of the Filter context, as it doesn't use it.

Ans if fact, it is precisely what we get:

Figure 4 – Result of the manipulated Measure (Figure by the Author)

The Total results would have been very different if Power Bi had summed up the values from the visual to calculate the Totals.

As the Filter Context is not used in the expression of the Measure, 1 is always returned, regardless of whether there are Sales.

Remember this when you look at the result later on.

Possible approaches

To manipulate the result of the Totals in this Visual, I must check for a Filter on the Geography hierarchy and the Product Class.

I can use the ISFILTERED() function to achieve this.

For example, when I want to get 1 for the column totals and 2 for the row totals:

Avg Retail Sales =
SWITCH(TRUE()
    ,NOT ISFILTERED('Geography' [StateProvinceName]), 1
    ,NOT ISFILTERED('Product'[ClassName]), 2
    ,AVERAGEX('Retail Sales', 'Retail Sales'[UnitPrice]*'Retail Sales'[SalesQuantity])
    )

This Measure performs the following checks:

If the column State is not filtered, the position is a column Total, and I want 1.
If the Product Class is not filtered, the position is a row Total, and I want
If both are filtered, I'm inside the Matrix (not at the Total) and want the Average Retail Sales amount.

This is the result:

Figure 5 – Result of the Measure which manipulates the Totals. I removed the Sum of Sales to simplify the Visual (Figure by the Author)

As you can see, this is a very Visual-specific solution.

As I have a Slicer filtering the Continent and Country, I cannot check for "NOT ISFILTERED(‘Geography')" because the Geography table is filtered when I select something from the Geography Slicer.

However, I can design a more generic solution if I know which tables or columns are usually used in my report.

For example, let's assume that the Product Category hierarchy and the Stores are usually used as row categories, and the Product Class, the Store Type, and the Color are used as column categories.

For example, like this:

Figure 6 – Modified report the Retail Sales by the Product Category hierarchy and the Store Types (Figure by the Author)

In this case, I can design two Measures to check for existing filters on these columns and hierarchies and use them in the SWITCH() call.

Here for the Row filter:

Row filter Check = 
ISFILTERED('Store')
||
ISFILTERED('Product'[ProductCategoryName])
||
ISFILTERED('Product'[ProductSubcategoryName])
||
ISFILTERED('Product'[ProductName])

And the same for the Column filter:

Column filter check = 
ISFILTERED('Store'[StoreType])
||
ISFILTERED('Product'[ColorName])
||
ISFILTERED('Product'[ClassName])

If I add these two Measures to the Matrix, I can see the result:

Figure 7 – Result of the Filter check Measure (Figure by the Author)

The Measure "Column filter check" works as expected. It's always True, except for the Total.

But the Measure "Row filter check" doesn't work as expected, as it returns True even for the total.

The reason is that the column Store Type is also from the Store table.

I must change the Measure to remove any filter on this column when checking for the filter:

Row filter Check = 
CALCULATE(
    ISFILTERED('Store')
    ,REMOVEFILTERS('Store'[StoreType])
    )
||
ISFILTERED('Product'[ProductCategoryName])
||
ISFILTERED('Product'[ProductSubcategoryName])
||
ISFILTERED('Product'[ProductName])

Now, the result is as expected:

Figure 8 – Correct result of the Filter check Measure (Figure by the Author)

Lastly, I can change the Measure to calculate the result by using these two Check Measures:

Avg Retail Sales =
SWITCH(TRUE()
    ,NOT [Row filter check], 1
    ,NOT [Column filter Check], 2
    ,AVERAGEX('Retail Sales', 'Retail Sales'[UnitPrice]*'Retail Sales'[SalesQuantity])
    )

And the result is as expected:

Figure 9 – Result with the target Measure (Figure by the Author)

The logic is slightly convoluted, as I must think about it in terms of columns and row totals. This is somewhat confusing if you are not used to it.

The next step is to insert the Measure, which returns the manipulated result different from the simple aggregation.

I can even choose to manipulate the grand total in a different way from the row and column total:

Figure 10 – DAX expression and result for manipulating the grand total (Figure by the Author)

As SWITCH() evaluates the checks one after another and returns the given result for the first expression that fulfills the condition, I must take care of the order of the checks for each variant.

One last little tweak

I can offer a minor tweak for those who dislike doing something if the check is false.

The Measure above checks for expression = FALSE.

I can change the check Measures to the following. Then I can avoid using NOT in the Measure:

Column filter Check =
IF(
    ISFILTERED('Store'[StoreType])
    ||
    ISFILTERED('Product'[ColorName])
    ||
    ISFILTERED('Product'[ClassName])
    ,FALSE()
    ,TRUE()
)

And

Row filter Check = 
IF(
    CALCULATE(
        ISFILTERED('Store')
        ,REMOVEFILTERS('Store'[StoreType])
        )
    ||
    ISFILTERED('Product'[ProductCategoryName])
    ||
    ISFILTERED('Product'[ProductSubcategoryName])
    ||
    ISFILTERED('Product'[ProductName])
    ,FALSE()
    ,TRUE()
)

Now I can get rid of the NOT inversion in the Measure:

Avg Retail Sales =
SWITCH(TRUE()
    ,[Row filter check] && [Column filter Check], 3
    ,[Row filter check], 1
    ,[Column filter Check], 2
    ,AVERAGEX('Retail Sales', 'Retail Sales'[UnitPrice]*'Retail Sales'[SalesQuantity])
    )

This approach is more intuitive to use than the one described above.

Conclusion

Sometimes, I encountered situations where I had to manipulate the result for the Total.

In such cases, it's essential to understand how Power BI works, how the Filter context is applied, and how the Totals are calculated.

I tried to explain how the Filter context works when looking at a Matrix.

But it's the same for any other Visual, which can use Categories to get Details in our Data.

I wrote an article about the functions available in DAX to explore the Filter context.

I strongly recommend you read it as well, as it might give you more tools to check the Filter context correctly:

Exploring the Filter Context with DAX functions

However, one important lesson is to avoid over-specific Measures, which can be used only for one visual. Instead, think outside the box to build more generic and reusable code.

This will certainly help for your future work.

References

Here is the link to the article mentioned at the beginning about calculating the weighted average:

To weigh or not to weigh – this is the Average question

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

I translated my client's request to the data in the ContosoRetailDW dataset.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: