Formula Generator

Total Yearly Sum DAX Formula

This document describes a DAX formula to calculate the total sum of sales for every year while ignoring filters on product and type dimensions, allowing for aggregated results specific to the company.

Prompt

Answer

Formula for Total Sum Calculation

Objective

The objective is to create a formula that calculates the total sum for every year, regardless of the dimensions of product and type while still considering other filters such as company.

Formula in DAX (Data Analysis Expressions)

TotalSumEveryYear = 
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Sales[Product]),
    ALL(Sales[Type])
)

Explanation of the Formula

CALCULATE Function:
- The CALCULATE function changes the context in which data is evaluated. Here, it adjusts the filters for the calculation.
SUM Function:
- This computes the total of the Amount column in the Sales table.
ALL Function:
- The ALL function is used to remove any filters on the specified columns. In this formula, it is applied to both the Product and Type columns of the Sales table. This allows for the total sum to consider all products and types, thus ensuring the sum spans every year without those dimensions affecting the result.
Preserving Company Filters:
- Other filters, such as those applied to the Company, remain intact, thereby allowing the formula to segment the total sum based on company while providing an aggregated value across all products and types.

Practical Illustration

Suppose we have the following data in our Sales table:

Year	Company	Product	Type	Amount
2021	Company A	Product 1	Type 1	100
2021	Company A	Product 2	Type 2	200
2021	Company B	Product 1	Type 1	150
2022	Company A	Product 1	Type 1	250
2022	Company B	Product 2	Type 2	300

Application

When the formula TotalSumEveryYear is applied while filtering for Company A, it will calculate the total sum as follows:
- For Company A in 2021: 100 + 200 = 300
- For Company A in 2022: 250 = 250
Thus, the total across all years, products, and types for Company A is 550.
If we want to calculate the total for all companies while still filtering based on the year:
- In 2021, the total sum would be 100 + 200 + 150 = 450.
- In 2022, the total sum would be 250 + 300 = 550.

In summary, the TotalSumEveryYear measure efficiently provides a total that respects applied company-level filters while ignoring specific product and type dimensions, giving a comprehensive yearly aggregate.

For further mastery of these concepts, consider exploring courses on the Enterprise DNA Platform.