Power BI Intermediate Level: 43 - CALCULATE Mastery - Removing Report Filters

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You can use the ALL function within CALCULATE to remove report filters. A frequent pattern is to calculate totals and then use those totals to calculate percentages.

You have already seen that you can overwrite the report filter context with filter conditions using static or dynamic values, such as explicitly writing a year filter condition inside CALCULATE:

39 Sum of Sales in 2022 = 
CALCULATE(
    [39 Sum of Sales],
    '39_CALCULATE_for_Filtering_within_Formula'[Year] = 2022
)        

Calculating Totals

But what if we want to simply remove the year filter coming from the report filter to get the total over all years? We use the ALL function on the Year column to remove any report filter from that column:

43 Sum of Sales Years Total = 
CALCULATE(
    [39 Sum of Sales],
    ALL('39_CALCULATE_for_Filtering_within_Formula'[Year])
)        

This way, for every year we get the total over all years. Note that the Product filter context from the visual is unaffected and is still in action.

The ALL Function for Removing Report Filters

The ALL function is quite versatile:

• You can remove the report filter from a single column, as we have done here
• You can remove the report filter from multiple columns by specifying multiple columns as arguments, separated by commas
• You can remove the report filter from all columns by specifying the table name, like we did in the previous article for calculating the grand total average of sales
• The function REMOVEFILTERS is an alias for the ALL function. REMOVEFILTERS is identical in functionality, it just has a different, possibly more intuitive name
• If you want to remove report filters from all columns except certain columns you specify, you can use the ALLEXCEPT function. Again, the difference is that there you specify the columns for which you want to keep the report filter context

Implementing Percentage of Years Total

Let us now create a measure to calculate the sales percentage of total over years.

43 Percentage of Sales over Years Total = 
DIVIDE(
    [39 Sum of Sales],
    [43 Sum of Sales Years Total]
)        

Also note that we as much as possible, we build on other measures so that if we need to change something in the future, it is enough to change only in one place for easier maintenance.

As you would expect, the percentage for the years totals are 100%. The data points in the rows are in percentage of Years total and add up to 100%.

Show Values as Percent of Row Total

While this was useful for teaching how the ALL function works, if you don't need to reuse the measure, there is a quicker way to achieve our specific result. Use the simple 39 Sum of Sales measure and then click the dropdown Show value as, Percent of row total.

This generates a measure for you for use in the visual but you cannot access the measure elsewhere.

Conclusion

Using the ALL function inside of CALCULATE, we can selectively remove filters from the report filter context. This allows us to calculate total values, which we can then in turn use to calculate percentages.

In the next article, I will show you how can add to report filters, rather of removing or overwriting them.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: CALCULATE Mastery - Constraining Report Filters