DAX Formula for Percentage of Cost Category on Total Costs

Objective

Calculate the percentage of a specific cost category relative to the total amount of costs in a dataset that also includes revenue items.

DAX Formula

To calculate the percentage, we can use the following DAX formula:

Cost Percentage = 
DIVIDE(
    SUMX(
        FILTER(CostsTable, CostsTable[Category] = "Desired Cost Category"),
        CostsTable[Amount]
    ),
    SUMX(
        FILTER(CostsTable, CostsTable[Type] = "Cost"),
        CostsTable[Amount]
    ),
    0
)

Explanation of the Formula

1. SUMX Function:

   • SUMX iterates over a table or a set of rows and evaluates an expression for each row, then returns the sum of those values.

2. FILTER Function:

   • The FILTER function creates a table that includes only the rows that meet specified conditions.
   • The first FILTER targets rows where the Category matches the desired cost category.
   • The second FILTER targets rows where the Type is defined as "Cost".

3. DIVIDE Function:

   • DIVIDE safely performs division while returning an alternate result (0 in this case) if the denominator is zero.
   • This function takes three arguments: the numerator (sum of the specified cost category), the denominator (sum of total costs), and the alternate result.

Practical Example

Assume we have a table named CostsTable with the following columns:

• Category (Text): e.g., "Marketing", "R&D", "Production"
• Type (Text): e.g., "Cost", "Revenue"
• Amount (Decimal): e.g., 500, 1500

To calculate the percentage of the "Marketing" cost category on the total costs:

• Replace Desired Cost Category with "Marketing".
• The formula calculates the total amount for "Marketing" costs and divides it by the total costs in the table.

Conclusion

This DAX formula efficiently calculates the desired percentage by filtering relevant costs and safely handling division, allowing users to derive insights from their financial data. For more advanced usage and learning, consider exploring the courses available on the Enterprise DNA Platform.