DAX Formula for Percentage of Cost Categories on Total Revenues

Overview

This document provides a DAX formula to calculate the percentage of cost categories with respect to total revenues. The data includes a column indicating the type of movement, which can either be "costs" or "revenue".

Requirements

• A table named FinancialData with at least two columns:
  • MovementType: This column indicates whether the entry is a "cost" or "revenue".
  • Amount: This column contains the monetary values for costs and revenues.

DAX Formula

The following DAX measure computes the percentage of costs with respect to total revenues.

CostPercentage = 
DIVIDE(
    CALCULATE(
        SUM(FinancialData[Amount]),
        FinancialData[MovementType] = "costs"
    ),
    CALCULATE(
        SUM(FinancialData[Amount]),
        FinancialData[MovementType] = "revenue"
    ),
    0
) * 100

Explanation of the Formula

• DIVIDE Function: This function is used to perform the division operation safely, providing an optional alternative result (0 in this case) if the denominator is zero.

• CALCULATE Function: This modifies the filter context of the data to sum the amounts conditionally based on the movement type.

  • The first CALCULATE sums up the Amount where the MovementType is "costs", yielding the total costs.

  • The second CALCULATE sums up the Amount where the MovementType is "revenue", yielding the total revenues.

• Multiplication by 100: This converts the resulting fraction into a percentage form.

Practical Example

Assume the FinancialData table contains the following data:

Calculation Steps

1. Total Costs:

   • SUM(FinancialData[Amount]) for "costs" = 200 + 300 = 500

2. Total Revenues:

   • SUM(FinancialData[Amount]) for "revenue" = 2000 + 3000 = 5000

3. Cost Percentage Calculation:

   • Cost Percentage = (500 / 5000) * 100 = 10%

Conclusion

The CostPercentage measure effectively computes the percentage of total costs relative to total revenues. This calculation helps in evaluating the cost performance within the organization regarding revenue streams. For enhanced learning and skills in DAX and data analysis, consider exploring resources available on the Enterprise DNA Platform.