Code Explanation: Cost Category Group Percentage Calculation

The provided code is written in DAX (Data Analysis Expressions), commonly used in Power BI and other Microsoft tools for data modeling and calculations. This code calculates the percentage of costs relative to total revenues, specifically filtering for a certain type of costs.

Code Breakdown

Components

1. DIVIDE Function:

   • Usage: This is a DAX function that performs division.
   • Parameters:
     • The numerator (the value to be divided).
     • The denominator (the value to divide by).
     • An optional value to return in case of division by zero (here, 0).

2. SUMX Function:

   • Usage: This is an iterator function that evaluates an expression for each row of a table and sums the results.
   • Parameters:
     • The table to iterate over (in this case, filtered movements).
     • The expression to evaluate for each row (the amount in Euros).

3. FILTER Function:

   • Usage: This function returns a table that has been filtered based on a condition.
   • Condition: It filters All_movements to include only those rows where the Type equals "B) Costs".

4. CALCULATE Function:

   • Usage: This function changes the context in which data is evaluated.
   • Parameters:
     • The expression to calculate (here, the sum of revenues).
     • The filter applied (which affects how the context for the calculation is established).

5. ALL Function:

   • Usage: Removes any filters from a specified table or column.
   • Context: Here, it ensures that the summation of revenues is not affected by any filters on Costs_Complete[Category group].

Explanation of Logic

The overall purpose of this code snippet is to compute the percentage of "B) Costs" from the total revenues.

1. Calculate Total Costs:

   • SUMX(FILTER(All_movements, All_movements[Type] = "B) Costs"), All_movements[Amount (Eur)])
   • This expression filters the All_movements table for entries of type "B) Costs" and calculates the total cost by summing their amounts in Euros.

2. Calculate Total Revenues:

   • CALCULATE(SUM(Revenues_Complete[Amount (Eur)]), ALL(Costs_Complete[Category group]))
   • This part sums the revenue amounts from the Revenues_Complete table without any filters on Costs_Complete[Category group].

3. Division:

   • The total costs are divided by total revenues, yielding the proportion of costs relative to revenues. The DIVIDE function ensures that if the denominator is zero, it returns zero instead of causing an error.

Key Concepts

• Context in DAX: The calculation is significantly influenced by the row context and filter context. Understanding how DAX interprets these contexts is vital for accurate calculations.

• Use of Functions:

  • Iterator vs Aggregator: SUMX is an iterator, allowing evaluation across many rows, while SUM simply aggregates values without row-by-row evaluation.

• Zero Division Handling: DAX’s DIVIDE function is particularly useful to prevent runtime errors when the denominator might be zero.

Alternative Example

To illustrate similar concepts, consider calculating the percentage of total sales against total expenses:

Sales Expense Percentage = 
DIVIDE(
    SUMX(FILTER(All_transactions, All_transactions[Type] = "Sales"), All_transactions[Amount (Eur)]),
    CALCULATE(SUM(Expenses_Complete[Amount (Eur)]), ALL(Expenses_Complete[Category])),
    0
)

In this example:

• The type "Sales" is filtered.
• The total expenses are calculated similarly, leveraging the CALCULATE and ALL functions.

Conclusion

This explanation has broken down the provided DAX code into its fundamental components, elucidating the overall functionality and methodology behind calculating the percentage of specified costs from total revenues. Understanding these foundational elements allows data professionals to adapt the code for various analyses within their data models. For further learning in DAX and related topics, consider exploring the resources provided on the Enterprise DNA Platform.