Calculation of Percentage of Category on Total Amount in DAX

Objective

To create a DAX formula that calculates the percentage contribution of a specific category to a total amount.

DAX Formula

The following DAX formula can be used to calculate the percentage of a selected category over the total amount:

Category Percentage = 
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category])),
    0
)

Explanation of the Formula

1. SUM(Sales[Amount]):

   • This part of the formula sums the values in the Amount column from the Sales table for the current category context.

2. CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category])):

   • The CALCULATE function modifies the context of the calculation.
   • Utilizing ALL(Sales[Category]) removes any filters applied on the Category column, allowing the summation to reflect the total amount across all categories.

3. DIVIDE:

   • This function safely divides the total amount for the current category by the overall total amount.
   • The third argument 0 specifies that if the denominator is zero (which prevents division by zero errors), the result will return 0.

Practical Example

Data Scenario

Suppose you have a Sales table structured as follows:

Application

• If you use the DAX formula in a Power BI report and focus on Category A, the calculation will proceed as follows:

1. Amount for Category A:

   • SUM(Sales[Amount]) for Category A = 100 + 150 = 250.

2. Total Amount for All Categories:

   • CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category])) = 100 + 200 + 150 + 300 = 750.

3. Final Calculation:

   • Category Percentage for A = 250 / 750 = 0.3333 or 33.33%.

Conclusion

This DAX formula is essential for dynamically calculating the contribution of each category towards the overall total amount in your analysis. This approach fosters a deeper understanding of proportionate values within your dataset. For further enhancements in data modeling and DAX proficiency, courses available on the Enterprise DNA Platform are highly recommended.