DAX Cost Percentage Calculation

DAX Formula for Percentage of Cost Categories on Total Revenue

Objective

To calculate the percentage of cost categories relative to the total amount of revenue in a dataset containing a column that classifies movements as either "costs" or "revenue."

DAX Formula

The following DAX formula achieves this calculation:

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

Explanation of the Formula

1. Variables:

   • TotalRevenue: This variable uses the CALCULATE function to compute the sum of the Amount column, filtered to include only rows where the MovementType is "revenue."
   • TotalCosts: Similar to TotalRevenue, this variable calculates the sum of the Amount column but filters for "costs."

2. Return Statement:

   • The formula returns the result of the DIVIDE function, which divides TotalCosts by TotalRevenue. The third argument in the DIVIDE function (0) specifies that if TotalRevenue is zero, it should return 0 instead of an error.

Practical Example

Assume you have a table named FinancialData structured as follows:

Using the above DAX formula:

• TotalRevenue calculation:

  • SUM of Revenue = 1000 + 2000 = 3000

• TotalCosts calculation:

  • SUM of Costs = 300 + 700 = 1000

• Percentage Calculation:

  • CostPercentage = DIVIDE(1000, 3000, 0) = 0.3333 or 33.33%

Conclusion

The DAX formula provided effectively calculates the percentage of cost categories against total revenue, offering insights into financial performance. For further learning and mastery of DAX and other data analysis techniques, consider exploring the courses on the Enterprise DNA Platform.