DAX Formula for Percentage of Grand Total

Formula

To calculate the percentage of the grand total for each row in a table, you can use the following DAX formula:

Percentage of Grand Total = 
DIVIDE(
    SUM(Table[Column]),
    CALCULATE(SUM(Table[Column]), ALL(Table)),
    0
)

Explanation

1. Function SUM(Table[Column]): This measure sums the values in the specified column for the current row context.

2. Function CALCULATE(SUM(Table[Column]), ALL(Table)):

   • CALCULATE changes the context in which the data is evaluated.
   • ALL(Table) removes any filters applied to the table, allowing SUM(Table[Column]) to compute the total sum across all rows, thereby representing the grand total.

3. Function DIVIDE:

   • The DIVIDE function safely performs division and returns 0 in the case of a division by zero error, preventing potential disruptions in calculations.

Practical Example

Suppose you have a table named SalesData with the following structure:

To apply the formula, create a new measure named Percentage of Grand Total as shown above.

Calculation Breakdown

1. For Product A:

   • Sales = 100
   • Grand Total = 100 + 150 + 250 = 500
   • Percentage = 100 / 500 = 20%

2. For Product B:

   • Sales = 150
   • Grand Total = 500
   • Percentage = 150 / 500 = 30%

3. For Product C:

   • Sales = 250
   • Grand Total = 500
   • Percentage = 250 / 500 = 50%

Thus, the calculated percentage for each product represents its contribution to the total sales.

Summary

• The provided DAX formula computes the percentage of total sales for each row based on the grand total.
• The use of DIVIDE and CALCULATE ensures accurate calculations and handles potential errors.
• This approach is useful for various data analysis contexts where relative contribution needs assessment.

For further enhancement of your DAX skills, consider exploring the Enterprise DNA Platform for additional resources and courses.