DAX Formula for Total Sum Ignoring Filters (Except Date Context)

Formula

The following DAX formula calculates the total sum of a specified column while ignoring all filters except those applied to date contexts:

Total_Sum_Ignore_Filter = 
CALCULATE(
    SUM(TableName[ColumnName]), 
    ALL(TableName),
    VALUES(TableName[DateColumn])
)

Explanation

Components of the Formula

1. CALCULATE: This function modifies the filter context of a calculation. It allows the use of multiple filter criteria together.

2. SUM(TableName[ColumnName]): This is the aggregation function that computes the total sum of the specified column from the table.

3. ALL(TableName): This part removes all filters applied to the entire table, effectively enabling us to calculate the total sum without any external filters, except for the ones defined next.

4. VALUES(TableName[DateColumn]): This function retrieves the distinct values for the date column while retaining the selected date filter context.

Logic

• By using ALL(TableName), all filters from other columns in the table are disregarded, allowing the calculation to consider every row in the specified column.
• The VALUES(TableName[DateColumn]) ensures that any active date filter in the report remains unchanged. Thus, the calculation is insulated against filters in all fields except the date context.

Practical Example

Scenario

Suppose you have a sales table named Sales, with the following columns:

• Sales[Amount]: The sales amount you want to sum.
• Sales[Product]: The product sold.
• Sales[Date]: The date of the sale.

Implementation

The formula for calculating the total sales amount regardless of product filters, while respecting date filters, would be:

Total_Sum_Sales_Ignore_Product_Filter = 
CALCULATE(
    SUM(Sales[Amount]), 
    ALL(Sales),
    VALUES(Sales[Date])
)

Usage in a Report

In a report setting, you can now place this measure (Total_Sum_Sales_Ignore_Product_Filter) in a visual that includes the Sales[Date] field. It will display the total sales amount for the current date context but will ignore any other filters applied to the report for the Sales[Product] field or any other dimension.

By implementing this DAX formula, you can achieve the desired analytical outcome while understanding how to manage filter contexts effectively in your data model. For further enhancement of DAX skills, consider the courses available at the Enterprise DNA Platform.