Freeing Your Calculations with ALL: When to Use It and How to Avoid Pitfalls

Let's talk about something liberating in this article! We'll look at one of the most powerful tools for manipulating filters and building advanced calculations: the ALL function. We'll see how it works, when to use it and the precautions you need to apply it efficiently.

What is the ALL function?

The ALL function removes the filters applied to one or more columns or to a whole table. It is often used in calculations that require filters to be ignored, such as grand totals, subtotals or percentages of the total, regardless of date or categorical filters, for example.

Syntax:

ALL(<Table or Column(s)>)

Practical example 1: grand total ignoring filters

Imagine you have a Sales table and you need the total amount sold, ignoring any applied filters. You can use the code below:

Grand Total =
SUMX(
    ALL(Sales),
    Sales[Value]
)

In this case, Grand Total returns the sum of all sales, regardless of the filters applied in the report.

Practical example 2: scenario with multiple filters

Suppose you want to create a measure that calculates the total sales, ignoring only the filters applied to the Category column while keeping all the other filters.

Total (Ignoring Category) =
CALCULATE(
    SUM('Sales'[Value]),
    ALL('Products'[Category])
)

In this case, ALL('Products'[Category]) removes the filters from the Category column only.

Practical example 3: percentage of the total

If you want to calculate each product's share of total sales, the ALL function is useful to ignore the filter context applied to the product.

Percentage of Total =
DIVIDE(
    SUM('Sales'[Value]),
    CALCULATE(
        SUM('Sales'[Value]),
        ALL('Products')
    ),
    0
)

Here, ALL('Products') removes the filters applied to the Products table, ensuring the denominator is the total sales of all products.

👍 Best practices for using ALL

• Plan the context: Use ALL when you need to ignore filters for global calculations. Always keep in mind the impact on the report context.
• Combine it with CALCULATE: ALL is often used together with CALCULATE to manipulate filter contexts efficiently.
• Avoid overuse: Don't use ALL indiscriminately on large tables, as it can hurt the model's performance.
• Document your measures: Make it clear in the measure name or in comments why you are using ALL. This helps with maintenance and with other people understanding your work.

🚨 Pitfalls when using ALL

• Performance impact: On large tables, using ALL can make calculations slower, especially when combined with iterator functions such as SUMX or AVERAGEX.
• Misreading the results: Make sure report users understand that calculations using ALL ignore filters. This can cause confusion in visual analysis.

Conclusion

The ALL function is an indispensable tool for building advanced calculations in Power BI. When used correctly, it lets you build robust analyses that ignore filters and bring new insights to your data. However, like any powerful function, it must be used with planning and care to avoid negative impacts on performance or on the clarity of the information.