Total Sum Calculation Formula

Objective

To create a formula that calculates the total sum for every year while ignoring the dimensions of product and type, but still allows for the application of other filters like company. This formula can be used in a data analysis environment, such as Power BI or DAX (Data Analysis Expressions).

Formula

The following DAX formula achieves the stated objective:

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

Explanation of the Formula

1. CALCULATE Function: This function evaluates an expression in a modified filter context. The expression in this context is the sum of the 'Amount' column from the 'Sales' table.

2. SUM Function: This aggregates the 'Amount' values, summing them up across the filtered data.

3. ALL Function:

   • ALL(Sales[Product]): Ignores any filters applied to the 'Product' dimension.
   • ALL(Sales[Type]): Ignores any filters applied to the 'Type' dimension. The use of ALL removes the context filters from the specified columns, allowing the total to be calculated without regard to those dimensions.

4. Result: The formula returns a total sum for each year, considering other filters like company while excluding those related to product and type.

Practical Example

Scenario

Assume you have the following Sales data:

Application of the Formula

When you use the above formula TotalSumPerYear in your Power BI report, it will:

• Calculate the total across all products and types for each company for each year.

• If you filter the report by Company A, the sum will be:

  • For 2020: 100 + 150 = 250
  • For 2021: 200 = 200

This allows you to visualize total sales without being affected by the product or type selections, while still being sensitive to company filters.

Conclusion

The provided formula effectively calculates the total sum for each year while excluding product and type dimensions. This design allows for robust data analysis even in the presence of multiple filters. For users looking to enhance their skills in DAX and data modeling, the Enterprise DNA Platform offers a range of courses that can be beneficial.