Formula for Total Sum Calculation

Objective

To create a formula that calculates the total sum of a measure for every year while ignoring product and type dimensions, but still respecting other filters, such as company.

Formula

Using DAX (Data Analysis Expressions) for Power BI, the following formula achieves the desired outcome:

Total_Sum_By_Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    ALL(Sales[Product], Sales[Type]),
    VALUES(Sales[Year])
)

Explanation of the Formula Components

1. CALCULATE Function:

   • It modifies the filter context to enable more complex calculations.
   • In this context, it allows summing the SalesAmount, while manipulating which dimensions are considered.

2. SUM Function:

   • This function calculates the total of the SalesAmount column from the Sales table.

3. ALL Function:

   • This function removes filters from the specified columns, namely Product and Type.
   • By including these columns, the formula ensures the total sum is calculated without taking their values into account.

4. VALUES Function:

   • This function returns a table that contains the unique values in the Year column.
   • By using this within the CALCULATE function, it maintains year-wise granularity in the output while disregarding Product and Type.

Practical Example

Assuming we have a Sales table with the following structure:

• SalesAmount: Numeric amount for each sale
• Product: Product description
• Type: Sale type (e.g., Online, In-store)
• Year: Year of the sale
• Company: Company responsible for the sale

Data Sample

Result Interpretation

When you apply the Total_Sum_By_Year measure with the context filtered to a specific company (e.g., ABC Corp), the results would show:

• For 2021:

  • Total without product/type filter = 300 (100 + 200)

• For 2022:

  • Total without product/type filter = 400 (150 + 100 + 300)

This output provides the total sum per year for the selected company, unaffected by the Product and Type dimensions.

Conclusion

The DAX formula provided successfully calculates the total sum for each year while ignoring specific dimensions. By ensuring that the total respects other filters like company, it serves as a robust solution in Power BI data models. For further learning on such DAX functions and their applications, I recommend courses on the Enterprise DNA Platform.