DAX Formula for Percentage of Cost Subcategories on Total Revenues

Objective

To calculate the percentage of costs (specific subcategories) relative to total revenue using DAX.

Data Structure

Assuming you have a data table with the following relevant columns:

• MovementType: Indicates the type of movement (either "costs" or "revenue").
• Amount: Contains the monetary value associated with each movement.

DAX Formula

The following DAX formula calculates the percentage of total costs relative to total revenues.

CostPercentage = 
VAR TotalCosts = 
    CALCULATE(
        SUM(Table[Amount]),
        Table[MovementType] = "costs"
    )
VAR TotalRevenues = 
    CALCULATE(
        SUM(Table[Amount]),
        Table[MovementType] = "revenue"
    )
RETURN 
    DIVIDE(TotalCosts, TotalRevenues, 0)

Explanation of the Formula

1. Variables Definition:

   • TotalCosts: This variable calculates the sum of the Amount where MovementType is "costs". The CALCULATE function changes the context of the data to only include rows with costs.
   • TotalRevenues: This variable sums the Amount where MovementType is "revenue", similarly using the CALCULATE function to filter the context.

2. Return Statement:

   • The formula uses the DIVIDE function to calculate the percentage by dividing TotalCosts by TotalRevenues. The third argument (0) specifies the value to return in case of division by zero, preventing any errors.

Practical Example

Assume you have the following data in your table named "Financials":

• Total Costs = 200 + 300 = 500
• Total Revenues = 1000 + 500 = 1500

Running the formula:

• CostPercentage = 500 / 1500 = 0.3333 or 33.33%

Conclusion

This DAX formula efficiently calculates the percentage of total costs in relation to total revenues, providing insights into the financial performance concerning cost subcategories. For further learning about DAX and its application, consider exploring relevant resources on the Enterprise DNA Platform.