DAX Formula for Calculating Percentage of Cost Categories on Total Amount of Costs

Formula

PercentageOfCosts = 
DIVIDE(
    SUMX(
        FILTER(
            Transactions,
            Transactions[Type] = "costs"
        ),
        Transactions[Amount]
    ),
    SUMX(
        FILTER(
            Transactions,
            Transactions[Type] = "costs" || Transactions[Type] = "revenue"
        ),
        Transactions[Amount]
    ),
    0
)

Explanation

1. SUMX Function:

   • This iterative function evaluates an expression for each row in a table and returns the sum of those values.
   • The first instance of SUMX is used to calculate the total amount of costs only, by filtering the Transactions table where the Type is "costs".

2. FILTER Function:

   • It creates a subset of the Transactions table, allowing for computations only on the relevant rows that match a specified condition.

3. Total Amount Calculation:

   • The second instance of SUMX calculates the total amount for both "costs" and "revenue" categories by applying a larger filter condition to include both types.

4. DIVIDE Function:

   • This function performs the division of the total costs by the total amount of both costs and revenue.
   • The third argument, 0, ensures that if the denominator is zero, it returns zero instead of an error.

Practical Example

Consider the following hypothetical Transactions table:

Execution of the Formula:

1. Total Costs:

   • From the above data:
     • Costs = 200 + 300 + 100 = 600.

2. Total Costs and Revenue:

   • Total = 200 (costs) + 500 (revenue) + 300 (costs) + 700 (revenue) + 100 (costs) = 1800.

3. Percentage Calculation:

   • Percentage of Costs = Total Costs (600) / Total (1800) = 1/3 = 0.333 or 33.33%.

Summary

The provided DAX formula allows for the calculation of the percentage of cost categories on the total amount of all costs and revenue categorised, making it a versatile tool for financial analysis in contexts where distinguishing costs from revenues is critical. For more advanced learning in DAX, exploring resources on the Enterprise DNA Platform can be beneficial.