DAX Formula for Calculating Percentage of Cost Subcategories on Total Revenues

Overview

The objective is to create a DAX formula that calculates the percentage of each cost subcategory relative to the total sales volume (total revenues). The analysis involves using three important components in the dataset:

1. Type of Movement - Identifies if the entry is a cost or revenue.
2. Subcategory of Costs/Revenues - Specifies the subcategory for costs or revenues.
3. Value of Movement - Contains the monetary value of each movement.

DAX Formulas

Step 1: Calculate Total Revenues

First, we will create a measure to calculate the total revenue.

Total Revenues = 
SUMX(
    FILTER(
        YourTable, 
        YourTable[Type of Movement] = "revenue"
    ),
    YourTable[Value of Movement]
)

Step 2: Calculate Total Costs

Next, we will create a measure to calculate the total costs.

Total Costs = 
SUMX(
    FILTER(
        YourTable, 
        YourTable[Type of Movement] = "cost"
    ),
    YourTable[Value of Movement]
)

Step 3: Calculate Cost Percentage

Finally, we will calculate the percentage of each cost subcategory on the total revenues.

Cost Percentage = 
DIVIDE(
    SUMX(
        FILTER(
            YourTable, 
            YourTable[Type of Movement] = "cost"
        ), 
        YourTable[Value of Movement]
    ),
    [Total Revenues],
    0
)

Explanation of DAX Formulas

Total Revenues

• SUMX: This function iterates over a table and evaluates an expression for each row, summing the result.
• FILTER: This function restricts the rows in YourTable to those where "Type of Movement" equals "revenue".

Total Costs

• Similar to the Total Revenues measure, this measure filters the table for entries where the "Type of Movement" is "cost" and sums the corresponding values.

Cost Percentage

• DIVIDE: This function computes the division of two numbers, with an optional alternative result (here, 0) in case of division by zero.
• The numerator sums up the values for the cost category based on the filter, while the denominator references the Total Revenues measure to express the relationship as a percentage.

Practical Example

Assuming you have a table named FinancialData structured as follows:

1. Total Revenues Calculation:

   • Total Revenue = 5000 + 3000 = 8000

2. Total Costs Calculation:

   • Total Costs = 2000 + 800 + 1200 = 4000

3. Cost Percentage Calculation for Manufacturing:

   • Cost Percentage (Manufacturing) = (2000 / 8000) * 100 = 25%

In this scenario, the DAX measures succinctly calculate the desired percentages through a clear and logical approach.

Conclusion

The above DAX measures effectively calculate the percentage of cost subcategories on total revenues. Implementing these formulas will enable detailed financial analysis and insights into cost management relative to sales performance. For further learning and mastery of DAX, explore the resources available on the Enterprise DNA platform.