Total Sum Calculation for Each Year

Objective

To calculate the total sum for every year regardless of the product and type dimensions, while still applying filters for other dimensions such as company.

Formula

Assuming we are using a data modeling language such as DAX (Data Analysis Expressions), the formula can be structured as follows:

TotalSumByYear = 
CALCULATE(
    SUM(Sales[Amount]), 
    REMOVEFILTERS(Sales[Product]),
    REMOVEFILTERS(Sales[Type])
)

Explanation of the Formula

• CALCULATE Function: This function changes the context in which data is evaluated. It allows for dynamic filtering and aggregation based on specified conditions.

• SUM Function: This is used to aggregate the sales amount. In this example, Sales[Amount] represents the column that contains the sales figures.

• REMOVEFILTERS Function: This function is crucial in ensuring that filters on the dimensions Sales[Product] and Sales[Type] do not affect the calculation of the sum by year. Instead, it will consider all available products and types in the calculation.

Practical Example

Scenario

You have a sales dataset with the following columns:

• Amount: Represents sales figures
• Year: Represents the year of the sale
• Product: Represents the type of product sold
• Type: Represents the type of sale (e.g., online, offline)
• Company: Represents the company name

Implementation

1. Data Table Structure:

   Year	Company	Product	Type	Amount
   2022	A	X	Online	100
   2022	A	Y	Offline	200
   2022	B	X	Online	150
   2023	A	Z	Online	300
   2023	B	Y	Offline	250

2. Using the Formula: When you apply the provided DAX formula, it calculates the total sum of Amount for each year, irrespective of which products or types are included in the filters.

   • If a filter is applied for Company A, the output will still provide the total sales amount for the years 2022 and 2023 without consideration for products or types.

3. Expected Results:

   Year	TotalSumByYear
   2022	450
   2023	550

Conclusion

This DAX formula allows you to analyze total sales on an annual basis without being impacted by product or sale type filters, enabling more comprehensive financial insights while maintaining the flexibility to filter by other dimensions such as company. For further proficiency in data modeling techniques, consider exploring the Enterprise DNA Platform, which offers valuable resources and courses.