Forum

What is the ALL fun...
 
Share:
Notifications
Clear all

What is the ALL function in DAX, and how can it help in creating dynamic calculations in Power BI?


Posts: 69
Guest
Topic starter
(@Vinay Kumar)
Trusted Member
Joined: 5 years ago

The ALL function in DAX is a useful tool for clearing filters in a calculation. It’s often used to calculate values like "percentage of total" because it can remove any applied filters and show totals across all data, regardless of filter context.

Syntax:

dax
ALL(<TableOrColumn>)
  • TableOrColumn: This can be a full table (like Sales) or a single column (like Sales[Region]), depending on where you want to clear filters.

Example 1: Calculate the Percentage of Total Sales

Let's say you want to calculate the percentage of total sales for each region. Using ALL will help you remove any filters on the Region column, allowing you to calculate the grand total sales across all regions:

dax
% of Total Sales =
DIVIDE(
SUM(Sales[SalesAmount]),
CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Region])),
0
)

In this formula:

  1. SUM(Sales[SalesAmount]) gives the sales for the current region in context.
  2. CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Region])) removes any Region filter and calculates the total sales for all regions.
  3. DIVIDE then divides the region sales by the total sales to give a percentage of total sales for each region.

Example 2: Using ALL with a Table for Grand Totals

Imagine you have a report where you need the grand total of all sales, ignoring any filters on the Date or Region. Using ALL on the entire Sales table will clear all filters and show the grand total:

dax
Total Sales (Ignore Filters) = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

This formula will always return the total sales across the entire table, regardless of any filters applied on Date, Region, or other columns. This is helpful for adding grand totals as reference points in reports.

Why and When to Use ALL?

  • Override Filter Context: ALL clears filters, making it perfect for comparisons, like finding the percentage of a total.
  • Grand Totals: Easily calculates totals that are unaffected by row filters in your visuals.
  • Comparison Calculations: Essential for scenarios like "difference from total" or "percentage of total," which need a fixed reference point.

Pro Tip: ALL is powerful but can make calculations slower in large datasets if used frequently. Use it strategically for calculations that genuinely need a clear filter context.

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Share: